grant vs revoke vs deny

https://www.mssqltips.com/sqlservertip/2894/understanding-grant-deny-and-revoke-in-sql-server/

Grant – gives perms
GRANT SELECT ON OBJECT::Test.TestTable TO TestRole;

Revoke- undoes perms whether it’s grant or deny
REVOKE SELECT ON OBJECT::Test.TestTable FROM TestRole;

Deny – blocks access and trumps all other access
DENY SELECTONOBJECT::Test.TestTable TO TestUser;

 

— Query sys.database_permissions to see applicable permissions

SELECT dp.class_desc, s.name AS ‘Schema’, o.name AS ‘Object’, dp.permission_name,        dp.state_desc, prin.[name] AS ‘User’ FROM sys.database_permissions dp   JOIN sys.database_principals prin     ON dp.grantee_principal_id = prin.principal_id   JOIN sys.objects o     ON dp.major_id = o.object_id   JOIN sys.schemas s     ON o.schema_id = s.schema_id WHERE LEFT(o.name, 9) = ‘TestTable’   AND dp.class_desc = ‘OBJECT_OR_COLUMN’ UNION ALL SELECT dp.class_desc, s.name AS ‘Schema’, ‘—–‘ AS ‘Object’, dp.permission_name,        dp.state_desc, prin.[name] AS ‘User’ FROM sys.database_permissions dp   JOIN sys.database_principals prin     ON dp.grantee_principal_id = prin.principal_id   JOIN sys.schemas s     ON dp.major_id = s.schema_id WHERE dp.class_desc = ‘SCHEMA’;

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.