What is the difference between deny and revoke in SQL server? Where exactly we can go for these two. Thanks all.
asked
what is difference between deny and revoke
DENY overrides other permissions and will prevent access to the object/action in question.
REVOKE simply removes the existing permission (irrespective of whether that permission was GRANTED or DENIED)
Where to go? BOL : DENY, REVOKE
Don't think of them as the same. You have either been GRANTED, or DENIED a permission, REVOKE reverses that.
As an example:
You grant a user 'Mary' EXECUTE permission on a Stored Procedure, but then realise you have made a mistake. If you REVOKE the permission then you are simply reverting back to the state before the GRANT. She may still have access to the proc through other group/role permissions. If you DENY, then you are explicitly DENYing Mary execute permission, irrespective of any other group/role permissions.
This was exactly the point of a 'question of the day' I posted on SSC.
Remember that you can REVOKE a permission and the user may still actually have that permission through a group the user is a member of. If you DENY a permission, that will take precedence and they really won't be able to do it (unless they have permissions to change permissions, then they can revoke the deny themselves, of course).
