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

  • You can do this with PoSH, but this is really a SQL proper thing.

    Why not just do it with SQL directly?

    A quick web serach using you post title would provide you a long list of this sort of thing.

    GRANT Object Permissions (Transact-SQL)

    Grants permissions on a table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym.


    How to Generate Grant Permission Script to User on Database Objects

    The will help to generate Grant Select,Execute & View permission to user on dataabse objects.We use this script when we have to give read only permission to users on prodcution databases


    Even if you wanted to the tis via PoSH you still have to call into SQL (SQLPS, sqlcmd, etc.) to do it.

    Out of the box PoSH does not have any SQL cmdlets, until you install them or you are directly on the SQL server where they are part of a SQL install.

    Script Individual User Rights in a Database with PowerShell

    Tonight on Twitter the call went out on #SqlHelp looking for a way to script just database permissions for a specific user.


    SQL login object permissions via PowerShell


    Verifying SQL Permissions in PowerShell


  • 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.