What is the difference between deny and revoke in SQL server? Where exactly we can go for these two. Thanks all.
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)
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.
No one has followed this question yet.