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;

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

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.

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

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