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.
