Like I mentioned previously REVOKE is like an undo function for GRANT and DENY. If you have a developer and you work with him for a while, you may find that he’s ready to be given a little more leeway in the database. Once you’ve decided to make a change to the permissions, you may want to undo just one or two of the permissions. It’s often better to undo a DENY permission, than to try and get a GRANT statement to fully replicate the same result. That’s why Microsoft provides the REVOKE command.
Let’s look at it’s syntax.
REVOKE
{
[ ALL [ PRIVILEGES ] ]
|
permission [ ( column(s) ) ]
}
[ ON securable ]
{ TO | FROM } principal [ ,...n ]
In case you’ve missed it on previous posts, the ALL keyword has been deprecated. Try to avoid using it if possible. Also, please note, ALL doesn’t actually cover every permission. Unless you’ve specifically identified the permission you’re trying to REVOKE in this case, you may not actually do what you’re intending with the ALL keyword. You’ve been warned 😉
The permission keyword covers the permission you’re trying to REVOKE. Please see the Books OnLine for a full list of permissions you can use here.
Again, if you’re using REVOKE for a table, view, or table-valued function, you can REVOKE permissions on specific columns in that object.
The ON securable refers to the actual object you’re trying to REVOKE a permission on. This can be any valid Microsoft SQL database object. Please note, different objects support different permissions. If you get an error in your REVOKE statement, you’ll want to consult the BOL for details on your specific usage.
TO or FROM is the same, Microsoft SQL supports both keywords to allow you to make more sense of your T-SQL statement. Sometimes it sounds better in English to say GRANT a permission TO a user, instead of GRANT a permission FROM a user. It’s all a matter of taste here folks!
Finally, the principal is your pal.
No, wait… That’s not it.
The principal is the user (or group) you’re making this change for.
That’s it, the basics of the REVOKE statement. Have any questions? Send them in! I’m here to help!