In SQL Server 2005 by default users of a database that are only in the public role cannot see the definitions of an object while using sp_help, sp_helptext or the object_definition function. Sometimes this is helpful to allow developers or other non-administrators to see the object definitions in a database, so they can create like objects in a test or development database. Instead of granting higher level permissions, is there a way to allow users that only have public access the ability to see object definitions?
When issuing sp_help, sp_helptext or using the object_definition() function the following errors in SQL 2005 will occur if the user does not have permissions to the see the object metadata. Here are a couple of examples of these errors.
|EXEC sp_help Customer|
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'Customer' does not exist in database 'MSSQLTIPS' or is invalid for this operation.
A select against the OBJECT_DEFINITION function will return a value of NULL if the user does not have permissions to see the meta data.
|SELECT object_definition (OBJECT_ID(N'dbo.vCustomer'))|
By default users were able to see object definitions in SQL Server 2000, but in SQL Server 2005 this functionality was removed to allow another layer of security. By using a new feature called VIEW DEFINITION it is possible to allow users that only have public access the ability to see object definitions.
To turn on this feature across the board for all databases and all users you can issue the following statement:
To turn on this feature across the board for all databases for user "User1" you can issue the following statement:
To turn this feature on for a database and for all users that have public access you can issue the following:
If you want to grant access to only user "User1" of the database you can do the following:
To turn off this functionality you would issue the REVOKE command such as one of the following:
If you want to see which users have this access you can issue the following in the database.
Here are two rows that show where the VIEW DEFINITION action has been granted. The first on a particular object and the second for all objects in the database.
To take this a step further, if you do not want to grant this permission on all objects the following stored procedure can be used to grant this to all objects or particular objects in a database. This is currently setup for all object types, but this can be changed by including less object types in the WHERE clause.
To use this, you can create this stored procedure in your user databases and then grant the permissions to the appropriate user instead of making things wide open for a user or all users. Just replace ChangeToYourDatabaseName for your database before creating.
Once this procedure has been created you can grant the permissions as follows. This example grants view definition to a user "userXYZ" in "MSSQLTIPS" Database for all object types that were selected.
For additional information on the topics discussed refer to these keywords in SQL Server 2005 Books Online:
- Create Procedure
- VIEW DEFINITION
Last Update: 9/29/2008
About the author
View all my tips