Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

1 Answer 1

up vote 0 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:

GRANT SELECT, VIEW DEFINITION ON SCHEMA::[dbo] TO [dev_datareader]
share|improve this answer
    
That's done the trick. Thanks, tchester. –  David Wimbush Nov 9 '09 at 16:40

Your Answer

 
discard

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.