36

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

Mehdi Charife
  • 722
  • 1
  • 7
  • 22
ceth
  • 44,198
  • 62
  • 180
  • 289

4 Answers4

25

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.

Mehdi Charife
  • 722
  • 1
  • 7
  • 22
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    Can I revoke DENY rule by REVOKE operation? – ceth Apr 25 '11 at 13:01
  • 9
    Yes, if you want to reverse the action of a DENY you ReREVOKE it (as counter-intuative as that may sound). Under the covers think of both GRANT and DENY as insert to the sysproctects and REVOKE is a delete from the sysprotects. – Ralph Shillington Apr 25 '11 at 13:03
  • 1
    @demas: yes, for example: `deny select on TestTable to TestUser; revoke select on TestTable to TestUser` – Andomar Apr 25 '11 at 13:06
  • Another one questions :) If I granted access to schema, can I revoke access to one table from this schema or the use DENY is the only one way to do it – ceth Apr 25 '11 at 13:15
  • *"REVOKE is an operation that removes a rule from the list of access rules."* Which rule is removed? The most recently added? – Mehdi Charife May 14 '23 at 15:34
  • What do you mean by object here? – Sergey Zolotarev Aug 19 '23 at 21:29
23

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.

Ralph Shillington
  • 20,718
  • 23
  • 91
  • 154
  • 5
    Revoke is not the opposite of grant. Deny is the opposite of grant. – Andomar Apr 25 '11 at 13:09
  • 3
    That depends on what you mean by 'opposite'. Revoke is the operation that will restore the state of an access list (that previously had no relevant rule) to what it was before a grant. – Random832 Apr 25 '11 at 13:41
  • 6
    As a programmer I understand it in the following way: **GRANT** is `+= Some (Read/Write/Manipulate) Rule`, **DENY** is `+= Some (Read/Write/Manipulate prohibition) Rule`, **REVOKE** is `-= Some (Any type) Rule`. – AlexMelw Jun 09 '17 at 15:59
11

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.

Mehdi Charife
  • 722
  • 1
  • 7
  • 22
Taylor Gerring
  • 1,825
  • 1
  • 12
  • 17
  • I am a bit new to Sql Server so i have the following question...Why do i have to DENY or REVOKE? Can't I simply remove the GRANT for that Insert action? – dim mik May 09 '18 at 14:17
  • @dimmik A massive bump, I realise, but revoking *is* what you do to "simply remove the GRANT from that Insert action". – Richard Ward Jan 31 '22 at 15:50
0

  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

sebkun
  • 1