revoke vs deny : what is the difference

Question

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

Bestir

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.

Beekeeper

Revoke is not the opposite of grant. Deny is the opposite of grant. – Hallowmas

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. – Trackman

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. – Gustavogustavus

Answer 2

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.

Hallowmas

Can I revoke DENY rule by REVOKE operation? – Bestir

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. – Beekeeper

@demas: yes, for example: deny select on TestTable to TestUser; revoke select on TestTable to TestUser – Hallowmas

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 – Bestir

"REVOKE is an operation that removes a rule from the list of access rules." Which rule is removed? The most recently added? – Euphemiah

What do you mean by object here? – Atomy

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.

Husha

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? – Outlandish

@dimmik A massive bump, I realise, but revoking is what you do to "simply remove the GRANT from that Insert action". – Sibyl

Answer 4

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

Roadhouse