Categories
MSSQL Server

MSSQL Check Effective Permissions

Check effective permissions on al securable using sys.fn_my_permissions(‘securable’, ‘class’). Class can be OBJECT, ROLE, SCHEMA, USER, etc.

EXECUTE AS LOGIN = 'DOMAIN\User'
SELECT * FROM sys.fn_my_permissions('dbo.TableName', 'Object')
REVERT

Create server or database role (QuickRef).

CREATE (SERVER) ROLE rolename
ALTER (SERVER) ROLE rolename ADD MEMBER [DOMAIN\User]

Grant or revoke permissions (QuickRef).

[GRANT|DENY|REVOKE] [SELECT|INSERT|UPDATE|DELETE|EXECUTE] (ON [dbo.TableName|SCHEMA::[SchemaName]) [TO|FROM] [Account|Role]
Example: GRANT SELECT, INSERT ON dbo.ViewName TO DOMAIN\User