Grant Deny Revoke

2

May 4, 2016 by Kenneth Fisher

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.

2 thoughts on “Grant Deny Revoke

  1. This was one of the first things I struggled with, thanks for laying it out so conciously.
    Especially the last paragraph:
    “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.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: