Deny vs Revoke

1

August 4, 2014 by Kenneth Fisher

Quick quiz. Which of these two commands is the opposite of GRANT?

  1. DENY
  2. REVOKE

 
Well lets start with some definitions

  • GRANT – Grants permissions on a securable to a principal.
  • DENY – Denies a permission to a principal.
  • REVOKE – Removes a previously granted or denied permission.

 
While I can really see some arguments either way in the end I would have to go with REVOKE as the opposite of both GRANT and DENY. If you look at the definitions both GRANT and DENY generate a permission rule while REVOKE removes that rule.

These two commands are fairly basic but you would be surprised how often people get them confused. As we see above DENY stops a user from accessing an permission. Except in a very few specific cases (sysadmin & dbo) a DENY will override a GRANT. This means that if a user is denied a permission they can not inherit a GRANT from another source.

-- Set up a login and user
CREATE LOGIN DenyTest WITH PASSWORD = 'DenyTest', 
     CHECK_POLICY = OFF;
GO
USE Test2;
GO
CREATE USER DenyTest FROM LOGIN DenyTest;

-- Set up a role that grants SELECT permissions to the database
CREATE ROLE GrantSelectRole;
GRANT SELECT TO GrantSelectRole;
EXEC sp_addrolemember 'GrantSelectRole','DenyTest';

-- Create a table with some values
CREATE TABLE Test (abc varchar(10));
INSERT INTO Test VALUES ('abcd');
INSERT INTO Test VALUES ('efgh');

Then in a window logged in as DenyTest

USE Test2;
GO
SELECT * FROM Test;

DenyTest_1

Next we DENY SELECT to the user

DENY SELECT TO DenyTest;

Run our test again

USE Test2;
GO
SELECT * FROM Test;

But this time we get an error

DenyTest_2

And in fact we can do the reverse (grant to the user & deny the role).

REVOKE SELECT TO DenyTest;
REVOKE SELECT TO GrantSelectRole;
DENY SELECT TO GrantSelectRole;
GRANT SELECT TO DenyTest;

And get exactly the same error.

DenyTest_3

But if I don’t include a DENY I can put the GRANT on the role or the user and the user will have the permissions needed.

REVOKE SELECT TO DenyTest;
REVOKE SELECT TO GrantSelectRole;
GRANT SELECT TO GrantSelectRole;

OR

GRANT SELECT TO DenyTest;

And we now have access again

DenyTest_4

So remember.

  • GRANT and DENY create a permission rule
  • REVOKE removes a permission rule
  • DENY always overrides a GRANT no matter what level the GRANT and DENY rules are placed.

 

BONUS: If you issue a GRANT that directly overrides a DENY (or vise-versa) the DENY is actually removed from the principal.

DENY SELECT TO DenyTest;
GRANT SELECT TO DenyTest;

The above code actually ends up with a single permission. SELECT is GRANTed to DenyTest.

If you run the opposite

GRANT SELECT TO DenyTest;
DENY SELECT TO DenyTest;

There is still a single permission rule but this time SELECT is DENYed to DenyTest

One thought on “Deny vs Revoke

  1. […] using Resource Monitor enough by Scott Hanselman SchemaBinding – What & Why by Kenneth Fisher Deny vs Revoke by Kenneth Fisher SQL Server Database Projects and Team Foundation Build by Steven Green Auditing in Azure SQL […]

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 2,151 other followers

Follow me on Twitter

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