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]

>GRANT SELECT, INSERT ON [dbo].[ViewName] TO DOMAIN\User
>GRANT EXECUTE ON [dbo].[Procedure] TO [RoleName]