3

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?

David Wimbush
  • 87
  • 1
  • 2
  • 7

2 Answers2

2

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]
tchester
  • 58
  • 1
1

Here's full SQL (with user creation)

CREATE LOGIN dev_datareader
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  
GO  

  CREATE USER dev_datareaderFOR LOGIN dev_datareader;  
GO  

GRANT SELECT, VIEW DEFINITION ON SCHEMA::[dbo] TO dev_datareader
Matas Vaitkevicius
  • 111
  • 1
  • 5