Granting And Revoking View Definition Permissions To A User In SQL Server

Understanding how to provide users access to various objects in a database is an important topic for anyone administering SQL Server.  Often times a developer or an analyst may need additional permissions granted on a database in order to perform their work.  From my experience this type of request usually comes when the user has been assigned a new project and needs visibility to a database that they have never worked with before.

Security should always be a top priority.  Understanding why a user needs access to something is vital as it allows you to make sure that you are providing the appropriate permissions without being too restrictive or too broad when assigning the new permissions.

With that in mind, I will illustrate the best way to grant a user VIEW DEFINITION permissions for both individual databases as well as all databases on the server.  For reference, VIEW DEFINITION allows a user to see the metadata of the securable to which the permission is granted.  It does not actually allow the user to access the securable itself.

When a user does not have sufficient VIEW DEFINITION permissions on a particular database they will get an error similar to what is shown below.

In this example I attempted to execute the sp_help command against the OperationRef database without sufficient VIEW DEFINITION permissions.  Though the error message could be a little more specific, it clearly lets you know something is wrong.

The scripts outlined below will give you a good idea as to how to appropriately grant the necessary VIEW DEFINITION permissions to allow users to see the database object definitions.

First we’ll start with the most broad option for granting access.  In order to grant VIEW DEFINITION for all databases to a specific user (we’ll call him Pedro) use the following script.

The next, more common, option would be to grant access for the user for a specific database.  The following script is similar to the first one but requires you to specify the database in the USE command as well as drop the ANY option from the GRANT command.

Keeping track of permission changes is also important.   If you want to review which users have permissions you can use the sp_helprotect command.  This will return the assigned permissions for each user based on the database from which it is run.

For example if I wanted to look for VIEW DEFINITION permissions assigned to the JDev database I would execute the following script.

The output will look something like this.  If you’re looking specifically for VIEW DEFINITION permissions you can look for it under the Action column.

 

Now that you understand how to GRANT the necessary permissions lets briefly discuss how to REVOKE those permissions.  Doing this is just as straight forward as the steps outlined above.

If you want to revoke VIEW DEFINITION permissions to the user across all databases the following script will accomplish this.

Similarly, if you want to revoke VIEW DEFINITION permissions to the user for a specific database, use the following script.

I hope this has helped shed some light on how to GRANT and REVOKE permissions in SQL Server.  Though this specifically references the VIEW DEFINITION permission set this logic can be applied to many different objects within SQL Server.