Grant User the Right to View Database Schema

While we normally frown upon granting backend access to our Production SQL Servers we have, on occasion, had a need to allow users to veiw the schema for objects within our Production databases. 

 

Example 1:  We have a “reporting” database that is refreshed nightly from a copy of our Production DB.  Report writers need to be able to veiw the table definitions to know which fields are available to them to build their reports. 

 

Example 2:  Errors were being generated in our production environment and they were believed to be related to improper versions of a couple of stored procedures having been released.  In order to do a schema comparison, quickly, between our development and production environments, to ensure all differences were corrected, we temporarily granted a developer “View Definition” access to allow them to perform a schema comparison.

 

Use the statement below to Grant User the Right to View Database Schema -

 

GRANT VIEW DEFINITION ON DATABASE::DBName TO UserName/RoleName

 

It’s a great way to allow the necessary access without having to grant elevated priveleges.

 

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s