CODEWITHSUNDEEP
sql-servert-sqldatabase-administrationsqlcommandsql-revoke
ceth
ceth

Reputation: 45325

revoke vs deny : what is the difference

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

Upvotes: 37

Views: 23968

Answers (4)

Andomar
Andomar

Reputation: 238196

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.

Upvotes: 26

Taylor Gerring
Taylor Gerring

Reputation: 1835

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.

Upvotes: 12

sebkun
sebkun

Reputation: 21

  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

Upvotes: 2

Ralph Shillington
Ralph Shillington

Reputation: 21108

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.

Upvotes: 23

Related Questions