Setting Permissions on a View via Powershell Script

All replies

• 

  

  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

  Reply

  |

  Quote
• 

  

  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

  Reply

  |

  Quote
• 

  

  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

  Reply

  |

  Quote
• 

  

  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

  Reply

  |

  Quote
• 

  

  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

  Reply

  |

  Quote
• 

  

  0

  Sign in to vote

  You can edit thee permissionset and reapply it many times.

  ---

  \_(ツ)_/

  Wednesday, February 28, 2018 10:34 PM

  Reply

  |

  Quote
• 

  

  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

  Reply

  |

  Quote