Can you please tell me how to set with Powershell the PermissionType : SELECT to ALTER? I'm attempting to use the:
Grant(ObjectPermissionSet, String, Boolean) - Grants access to the specified permission and the ability to grant access to other users for the specified View Class (object) I've setup - $vwobject.Grant(ALTER, Grantee, $TRUE)
Below is the result of a query of the permissions assigned to the View = TestView2 which I'd like to have multiple users assigned the same permission set - but for now, I'm just trying to Change SELECT to ALTER just to understand how to change or add to one property. Not understanding how the View.Grant method works syntactically - your help please?
$vwObject.EnumObjectPermissions()PermissionType : SELECTGrantee : SUTTER-CHS\InfaDev_serviceGranteeType : UserGrantor : dboGrantorType : UserPermissionState : GrantColumnName :ObjectClass : ObjectOrColumnObjectName : TestView2ObjectSchema : dboObjectID : 1510683327
There are a number of ways to accomplish this, but it looks like you're using SMO. There are still a couple of ways to use that.Understand that permissions are individual and you can't just change one to another. They are set individually.
$svr = new-object Microsoft.SqlServer.Management.Smo.Server $servername;$db = $svr.databases[$dbname];
So, from this point there are two ways to set a permission. First just do a SQL query:
$query = "grant alter on [TestView2] to <username>";$db.executenonquery($query);
The second way:
$vwObject = $db.views["TestView2"];$permObject = New-Object ('Microsoft.SqlServer.Management.Smo.ObjectPermissionSet');$permObject.Alter = $true;$view.grant($permObject, <username>);
Turn off SELECT permissions:
$permObject.Select = $false;$view.grant($permObject, <username>);
Thank you for explaining to me this method and using the example to complete this task for my project. Adding the Turn off helps too.