Thursday, January 27, 2011

In SQL Server how to give only "read only" permission to all DB objects?

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?

  • 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.

    From Jeff Siver
  • Assuming you want to grant the rights to view everything under the dbo schema:

    GRANT VIEW DEFINITION ON schema::dbo TO [UserName]
    
    Faiz : Will that allow selecting records from tables? Or did you mean a db_DataReader + this GRANT option?
    Lloyd McFarlin : If you need the users to also select data, you'll need to issue the statement I provided along with DataReader.
    Lloyd McFarlin : 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.
    Faiz : That worked! Thank you Lloyd :)
    Lloyd McFarlin : Glad to hear it! :)

0 comments:

Post a Comment