SQL permissions

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Anyone help me with this? I'm struggling to define the difference between DENY and REVOKE permission clauses.

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_2un9.asp

One creates the denial, while the other removes the denial or granted permissions. Is this helping at all?

 

This is a little tricky actually.

I think of grant and deny as bit fields and the grant and deny statements as functions that just set the boolean flag the field represents to on/true. The tricky part is remembering that a set deny flag always overrides a grant. The revoke statement I think of as a reset or clear function. Calling it resets the grant and revoke flags back to 0 (off/false). So, the moral of story is: if you want to make sure a grant takes effect, always clear any deny flags with revoke first! ;)

 

A deny explicitly denys whatever access or permission that is being referenced. A revoke removes an existing access or permission whether it be a deny or allow.

Say you have a user who belongs to two different groups. One is denied access to the database and on is permitted access to the database. The deny overrides the allow and the user is denied access. However, if the deny is revoked then the user is allowed access as the deny is no longer in effect.

 

Conceptually it's easy to fall into the trap of thinking of grant as turning a switch on and deny turning the same switch off, but that's not how it works. They're seperate switches. Grant and deny statements can only turn the switches on. To turn either off you must use revoke. If you can remeber that and the fact that deny always overrides grant, you should be OK.

 

Thanks all. Got it now.

Something of a Yes/No/Maybe situation!
Grant = Yes
Deny = No
Revoke = Maybe (depends what the other permissions say).