# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases>
[reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")
# Step 2 - We create a variable to instantiate the object
permssion
PS SQLSERVER:\sql\xu_xps\default\databases>
$s=new-object
microsoft.sqlserver.management.smo.DatabasePermissionSet
# Step 3 - Check the properties and methods avialable to the
bject
PS SQLSERVER:\sql\xu_xps\default\databases>
$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 schemas
PS SQLSERVER:\sql\xu_xps\default\databases>
$S.Select=1
PS SQLSERVER:\sql\xu_xps\default\databases>
$S.Execute=1
PS SQLSERVER:\sql\xu_xps\default\databases>
$S.ViewDefinition=1
# The script below works fine in SQL Server 2008/R2, however,
the script{$s) causes an error in SQL Server 2012
# the database user xu_xps\charlie should now have select,
execute, and view defintion permissions on all of the databases if the
databases have that user account
PS SQLSERVER:\sql\xu_xps\default\databasess>
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>
$S.Select=0
PS SQLSERVER:\sql\xu_xps\default\databases>
$S.Execute=0
PS SQLSERVER:\sql\xu_xps\default\databases>
gci |
% {$_.Grant($s,"xu_xps\charlie")}
