Blog Post

Grant Deny Revoke

,

It always amazes me how often these three commands get confused. Heck it amazes me how long I confused them.

TL;DR; GRANT and DENY are opposites. GRANT applies a positive permission, DENY a negative permission. DENY will always override GRANT. REVOKE is the opposite of GRANT and DENY. It removes a permission.

GrantDenyRevoke

To start with GRANT and DENY are opposites.

GRANT

Applies a positive permission.

GRANT SELECT TO [MyUser];

This allows MyUser to run a SELECT statement against any table, view or table valued function in the database.

DENY

Applies a negative permission.

DENY SELECT TO [MyUser];

This means that MyUser can not run a SELECT statement against any table, view or table valued function in the database.

That probably doesn’t sound like you are applying a permission does it? And that is probably where a lot of the confusion comes in. If, however, we take a look at the system views where the data resides then we can see proof that both commands, GRANT and DENY, add a permission.

SELECT Perms.* 
FROM sys.database_permissions Perms
JOIN sys.database_principals Users
ON Perms.grantee_principal_id = Users.principal_id  
-- or sys.server_permissions and sys.server_principals
-- if we were looking at server level permissions
WHERE Users.name = 'MyUser';

GrantDenyRevoke2

See how there is an entry covering the DENY permission? If we run the GRANT and then re-run the query we will see a GRANT entry instead of the DENY.

REVOKE

Removes a GRANTed or DENYed permission.

REVOKE SELECT TO [MyUser];
SELECT Perms.* 
FROM sys.database_permissions Perms
JOIN sys.database_principals Users
ON Perms.grantee_principal_id = Users.principal_id  
-- or sys.server_permissions and sys.server_principals
-- if we were looking at server level permissions
WHERE Users.name = 'MyUser';

GrantDenyRevoke3

And you can now see that the DENY entry is no longer there. Note that you don’t have to specify that you are revoking the DENY. Just that you are revoking the permission. Further proof that DENY is also a permission (if a negative one).

So GRANT is the opposite of DENY and as a pair they are the opposite of REVOKE.

One last point. You may have wondered why I always make DENY bigger than GRANT. This is meant to be a visual clue for everyone. DENY will always override a GRANT. If you a granted a permission at the object level, database level, and through 47 different AD groups and database roles one DENY will still override all of them.

Hopefully that clears up some of the confusion.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: microsoft sql server, security

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating