revoke vs deny : what is the difference

Question

What is the difference between the DENY and REVOKE commands in SQL Server?

Answer 1

Revoke is the opposite of a Grant (at least in as much as Grant adds an access rule and Revoke Removes an access Rule) While somewhat counter-intuative Deny also adds an access rule (which of course can be removed with a Revoke).

If I grant the sales group access I can later revoke it.

However I could also deny you access, and even through you're in the sales group you'll not have access.

Answer 2

1. Granting Permission means that a user can access the object

2. Denying permission overrides a granted permission

3. Revoking a permission removes the permission that has been assigned, regardless of whether it was a denied permission or a granted permission

Answer 3

REVOKE removes access that has been GRANTed. DENY explicitly rejects, taking precedence over GRANTs.

To the last point, if someone is part of the db_denydatawriter role, but you GRANT INSERT to them, the DENY will override that GRANT and they will be unable to INSERT.

Answer 4

Each object has a list of rules DENYing and GRANTing access.

REVOKE is an operation that removes a rule from the list of access rules.