Granting View Object Permissions via SQL PowerShell

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? 

PermissionType  : SELECT
Grantee         : SUTTER-CHS\InfaDev_service
GranteeType     : User
Grantor         : dbo
GrantorType     : User
PermissionState : Grant
ColumnName      :
ObjectClass     : ObjectOrColumn
ObjectName      : TestView2
ObjectSchema    : dbo
ObjectID        : 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>";

    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. 

Reply Children
No Data