Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Deny vs Revoke

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


Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: code language, database permissions, language sql, microsoft sql server, security, T-SQL

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...