Friday, May 28, 2010

Understanding GRANT, REVOKE and DENY T-SQL Commands

GRANT, REVOKE, and DENY commands are T-SQL commands in SQL Server for managing permission. Although we know the correct usage of them, REVOKE and DENY have confused most of us, what exactly SQL Server does for these two commands. Here is a brief explanation on them;

GRANT – Let users to perform an operation on objects.

REVOKE – Removes assigned GRANT permissions on an object for one or more operations. Main thing you have remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object for the operation, user will be able to perform the operation.

DENY – Denies permission to the object for an operation. Once it set, since it takes precedence over all other GRANT permissions, user will not be able to perform the operation against the object.

If you have given the above set of permission, the user LetMeStartU will be able to create tables. Only thing is, user will see warnings when try to create tables from Management Studio but tables can be created and saved. I tested this, it works.

But user will not be able to open it again and alter it unless you have given VIEW DEFINITION permision to him. Once it is given, altering tables is possible too.

Just to clarify, there is another facet to the description of grant-revoke-deny that you may have missed out.

GRANT and DENY are used to assign explicit permissions. REVOKE is to remove the explicit granted or denied permission. In other words, GRANT or DENY is to insert a row to some table (that is used to look up permissions) and REVOKE is to delete the associated GRANT or DENY row.

So generally we do not use revoke in place of deny though sometimes it might seem interchangeable.

Search This Blog

About Me

Dinesh Priyankara (MSc IT) is an MVP – Data Platform (Microsoft Most Valuable Professional) in Sri Lanka with 16 years’ experience in various aspects of database technologies including business intelligence. He has done many local and foreign business intelligence implementations and has worked as a subject matter expert on various database and business intelligence related projects. He is the Founder/Principal Architect of dinesQL (Pvt) Ltd and he consults, teaches and runs training programs on data related solutions and subjects.