How to Change Permissions on Objects in SQL Server using Powershell?

# Requirement: removing permissions from individual SPs and put the permission at the schema level.

# I am in a sysadmin role. There is a login called PSTest and a db user in Adventureworks 2014, also called PSTest.

# Option 1

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | %{$SP=$_.Schema+"."+$_.Name; $SP; Invoke-sqlcmd -query "revoke execute on $sp

 PSTest" -SuppressProviderContextWarning}

# Option 2 - the SMO approach. Let's check the revoke method in SMO first

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | gm |? {$_.name -eq "revoke"} | fl

# One of the overloaded method for revoke is void Revoke(Microsoft.SqlServer.Management.Smo.ObjectPermissionSet permission, string[] granteeNames)

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> $a=new-object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> $a

Alter              : False

Control            : False

Connect            : False

Delete             : False

Execute            : False

Impersonate        : False

Insert             : False

Receive            : False

References         : False

Select             : False

Send               : False

TakeOwnership      : False

Update             : False

ViewDefinition     : False

ViewChangeTracking : False

CreateSequence     : False

# Change the property for Execute

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> $a.Execute=1

# Grand the permission for all SPs

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | %{$_.name;$_.grant($a,"PSTest");$_.Alter()}

# Revoke the permission for all SPs

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | %{$_.name;$_.revoke($a,"PSTest");$_.Alter()}

# How to trap the meaningful error message (The internal error message from SQL Server. Execute the script in PowerShellISE

cd SQLServer:\sql\localhost\default\databases\Adventureworks2014\StoredProcedures

$a=new-object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet

$a.Execute=1

#$a.Execute=0 # For testing the detailed messsage

#$a.Insert=1  # Fortesting the detailed messsage - Insert is not applicable to a sp

$acct = "PSTest"

dir | % {$_.Revoke($a,$acct) ###########$_.Grant($a,$acct)

         $_.Name;

         $_.Alter();

         Trap {

                $err=$_.Exception

                $err.message

                while ($err.InnerException)

                {

                    $err=$err.InnerException

                    write-output $err.message

                }

                Continue

               }

        }

cd c:\

# Or save the script as TrapTheError.ps1 and execute the file from the right location

PS C:\users\xu-acer\documents> ./TrapTheError.ps1