hi guys just a quick overview i cannot seem to find a brief overview of the following for SQL server 2008 R2. What is the difference between GRANT WITH GRANT DENY I am a member of the sysadmin role so i have access to everything. If i want to give a new user permissions to a table that is not in a server role (like sysadmin) do i HAVE to specifically give GRANT access or DENY if i don't want them to have access? I don't get it because if i don't give GRANT then surely they are already denied? Doesn't make sense to me. Either give them access or not, 1 or 0 i don't understand the need for GRANT and DENY. If i don't GRANT or DENY are they DENIED by default? Hope someone can clear this up!
GRANT specifically allocates permissions to access an object WITH GRANT further allows those you've granted permissions to grant permissions to others. You probably don't want to do this. DENY specifically forbids access to an object. This is not the same as REVOKE - which removes any previous GRANT. If you haven't specifically GRANTed access to an individual, then the system will look at any roles and user groups of which the user is a member to get the permissions appropriate to that user.
and to add to @ThomasRushton's great answer - a DENY takes precedence over other permissions. So yes you are right, if you haven't granted access, then the user does not have access, but if you DENY, and then later on GRANT, without revoking the DENY, they will still be denied. Don't think of the security setting as 1 or 0, it's more like -1, 0, 1
To add to ThomasRushton's and Kev's excellent answers, the server does not even check the permissions for someone that is in the sysadmin fixed role or, for the database, the dbo role. This means that while Deny takes precedence over grant, a sysadmin will not be affected by the deny.