Re: Execute Permissions

Donabel Santos — Tue, 16 Nov 2010 06:00:00 GMT

Hi Khellian:

Have a look at the snippet below. You can use this as a starting point.

#please note I am using SQLPSX here to simplify some of the tasks, such as
#creating a database handle, or getting the stored proc collection



#SQLPSX addin from codeplex: http://sqlpsx.codeplex.com/
#SQLPSX documentation is here: http://www.sqlpsx.com/


$server = "POSEIDON"
$database = "test2"
$storedProcs = Get-SqlDatabase $server $database | Get-SqlStoredProcedure


#have a look at this for possible values for PermissionSet
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.objectpermissionset.aspx

#next line should be just one line
$permissionSet =New-Object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet(
                           [Microsoft.SqlServer.Management.Smo.ObjectPermission]::Execute);


#now let's loop through all stored procs in your database
foreach($sp in $storedProcs)
{
   $sp.Revoke($permissionSet, "public");



   #to grant, syntax would be similar to:
   #$sp.Grant($permissionSet, "granteeName");
}

Hope this helps!

Donabel

Search results for '(app:forums OR groupid:24)' matching tags 'Stored Procedure' and 'SMO'

Re: Execute Permissions