I need to give read only permission to a couple of users on the database so that they can get an understanding of the schema, logic in SPs, etc. But I do not want them to modify anything. I tried assigning the db_datareader role but it doesn't allow viewing SP name or code. What is the right role-combination to do this or do I need to write a T-SQL script to achieve this?
views:
1247answers:
2
A:
I believe you will have to write a TSQL script to grant view on the SP's. DB_DataReader only gives read access to the user tables; it doesn't include any other rights. And I know of no included database role or server role that will do what you are asking.
Jeff Siver
2009-07-31 06:26:07
+1
A:
Assuming you want to grant the rights to view everything under the dbo schema:
GRANT VIEW DEFINITION ON schema::dbo TO [UserName]
Lloyd McFarlin
2009-07-31 06:38:34
Will that allow selecting records from tables? Or did you mean a db_DataReader + this GRANT option?
Faiz
2009-07-31 07:47:13
If you need the users to also select data, you'll need to issue the statement I provided along with DataReader.
Lloyd McFarlin
2009-07-31 19:47:03
Also, note that my usage of GRANT VIEW DEFINITION is just one example, really. I suggest reading up on this command in SQL Books Online for using it with a different level of scope, if needed.
Lloyd McFarlin
2009-07-31 19:48:19
That worked! Thank you Lloyd :)
Faiz
2009-08-04 05:22:20
Glad to hear it! :)
Lloyd McFarlin
2009-08-04 05:37:11