Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a staging server where we do final acceptance testing before we go live. It's a copy of Live where devs have no more privileges than regular users. I'm trying to give the devs read-only permissions on data and objects on this server so they can investigate bugs.

I put them in a role that is a member of db_datareader which gives them data and table schema, but they can't see what's in the views, stored procs and functions and so they keep coming to me to find out. I can't add them to db_ddladmin because that would let them alter objects, and I can't see any other fixed server or database roles that look right.

Do I really have to grant them VIEW DEFINITION on every object in question or is there a cleaner way?

share|improve this question
up vote 1 down vote accepted

You can grant the "View Definition" permission at the schema level. The easiest way is to create a new role in your staging database (say "dev_datareader"). Then you execute the following, substitute your schema name for "dbo" if it is different:

share|improve this answer
That's done the trick. Thanks, tchester. – David Wimbush Nov 9 '09 at 16:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.