Alright, at this point you should know how to GRANT permission to a database object, but do you know how to DENY permission to an object? DENY uses a syntax similar to GRANT.
DENY { ALL [ PRIVILEGES ] }
| permission [ (column(s) ) ]
[ ON securable ] TO principal
I want to let you know two things about the ALL keyword here. First, ALL is deprecated as of SQL 2008. This will be going away in a future version of Microsoft SQL, so don’t use it. Get used to it not being there, then your code won’t suffer any major failure during a future upgrade. Second, DENY ALL doesn’t actually block the user from everything. Check out the MSDN Books OnLine, you’ll find that this blocks a good deal of access, but there will be some permissions left. That’s another good reason to skip the ALL keyword.
The next keyword(s) you’ll need to include concern themselves with the action you’re trying to block access to. This is where you would identify INSERT, UPDATE, SELECT, ALTER, etc. Again, see the Books OnLine for a complete list.
The column list applies pnly when you’re denying access to a table, view, or table-valued function. You can include a list of columns in parentheses, and DENY access to SELECT, UPDATE or REFERENCES for that column. You can include this column list in the permission clause, or just after the securable clause. Both are valid.
The securable is the object you’re trying to secure, it can be any valid Microsoft SQL object. Please note, different objects support different levels of lock-down. When in doubt, check out the BOL.
The TO principle is the user, group, etc you’re applying this DENY statement to.
I know this is just the basics, but I want you to consider the fact that anything you can GRANT, you can reverse that with DENY. In a future post, I’ll introduce you to REVOKE… it’s sort of like an undo command for GRANT or DENY.
If you have any questions, send them in, I’m here to help!