# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
[reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")
# Step 2 - We create a variable to instantiate the object permission
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$s=new-object
microsoft.sqlserver.management.smo.ObjectPermissionSet
# Step 3 - Check the properties and methods available to the object
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$s | gm
# Step 4 - Now we can set some properties for the permission
object. Let's we want to grand Select, Execute, and View Definition to xu_xps\charlie
for all the schemmas
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$S.Select=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$S.Execute=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$S.ViewDefinition=1
# The script below works fine in SQL Server 2008/R2, however,
the script{$s) causes an error in SQL Server 2012
# xu_xps\charlie should now have select, execute, and view definition
permissions on all of the schemas in the current PS session and the database
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
gci |
% {$_.Grant($s,"xu_xps\charlie")}
# Step 5 - Now let's set Select and Execute to false (leaving
ViewDefinition to true)
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$S.Select=0
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
$S.Execute=0
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
gci |
% {$_.Grant($s,"xu_xps\charlie")}
<# You thought the Select and Execute permissions for
Charlie on all of the schemes are changed to false.
Unfortunately they
don't. All of the schemes still have Select, Execute, and View Definition permission.
The changes are only
effective in the PS session, they do not affect the values in the database even
with the following command:
gci | %
{$_.Grant($s,"xu_xps\charlie");$_.Alter();S_.Refresh()}. But when you
execute the revoke command, it flips the values
for Select, Execute,
and View Definition in the current PS session and that will hit the database,
making the permanent changes.
As the current value
for Select, Execute, and View Definition is False, False, and True. The revoke
command will make them True, True, and False in the database.
#>
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas>
gci |
% {$_.revoke($s,"xu_xps\charlie");$_.alter();$_.refresh()}
