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?
Best Answer
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: