Asked by:
Setting Permissions on a View via Powershell Script

Question
-
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 : 65536And 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 : 65536What 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
-
On what? We can't guess at the objects you are trying to show.
\_(ツ)_/
Wednesday, February 28, 2018 12:42 AM -
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 -
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
-
You would have to use the "grant/revoke" to alter the view permissions.
\_(ツ)_/
- Edited by jrv Wednesday, February 28, 2018 3:00 PM
Wednesday, February 28, 2018 2:57 PM -
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 -
You can edit thee permissionset and reapply it many times.
\_(ツ)_/
Wednesday, February 28, 2018 10:34 PM -
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