Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Setting Permissions on a View via Powershell Script RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    After listing out the permissions via $vwObject.EnumObjectPermissions()

           PermissionType : ALTER
           Grantee : Endpoint-Admins
           GranteeType : ServerRole
           Grantor : BASE\ctrlb
           GrantorType : Login
           PermissionState : Grant
           ColumnName :
           ObjectClass : ObjectOrColumn
           ObjectName : TestView2
           ObjectSchema :
           ObjectID : 65536

    And you need to change it to 

           PermissionType : SELECT
           Grantee : Endpoint-Users
           GranteeType : User
           Grantor : dbo
           GrantorType : User
           PermissionState : Grant
           ColumnName :
           ObjectClass : ObjectOrColumn
           ObjectName : TestView2
           ObjectSchema : dbo
           ObjectID : 65536

    What is an example for how to use permissionSet to do so, please?


    • Moved by Bill_Stewart Monday, April 30, 2018 9:39 PM This is not "teach me how to use SQL from PowerShell" forum
    Wednesday, February 28, 2018 12:35 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    On what?  We can't guess at the objects you are trying to show.

    \_(ツ)_/

    Wednesday, February 28, 2018 12:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Form SMO View object use "Grant" and "Revoke"

    https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.view.aspx

    \_(ツ)_/

    Wednesday, February 28, 2018 12:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    If I Enumerates a list of object permissions for a specified grantee and a specified permission on the view and the values need to be changed.   Like PermissionType : ALTER needs to become  PermissionType : SELECT using Powershell.  I'm searching for example code that shows how this is done.
    Wednesday, February 28, 2018 2:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You would have to use the "grant/revoke" to alter the view permissions.

    https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/granting-revoking-and-denying-permissions

    \_(ツ)_/


    • Edited by jrv Wednesday, February 28, 2018 3:00 PM
    Wednesday, February 28, 2018 2:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for pointing me to how to do a grant method.  I'm not sure how to copy these permissions from DOMAIN\User1 to DOMAIN\User2  Will I need to create a PermissionSet for each of the 11 assignments for TestView2? 

    Could you please show me how you would do two of these in a PowerShell script?


    $vwObject.EnumObjectPermissions()

    PermissionType  : SELECT
    Grantee         : DOMAIN\User1
    GranteeType     : User
    Grantor         : dbo
    GrantorType     : User
    PermissionState : Grant
    ColumnName      :
    ObjectClass     : ObjectOrColumn
    ObjectName      : TestView2
    ObjectSchema    : dbo
    ObjectID        : 1510683327


    Wednesday, February 28, 2018 10:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can edit thee permissionset and reapply it many times.

    \_(ツ)_/

    Wednesday, February 28, 2018 10:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It would actually be easier to just use SQL to grant permissions.  Just write the statement and give it the user as a variable.

    \_(ツ)_/

    Wednesday, February 28, 2018 10:35 PM