I "want" to allow a developer to view stored procedures in the production environment without giving him permission to delete, edit or create stored procedures in the production environment. How can I do this?
oops - SQL2005
TIA
Al
asked
Permission to view stored procedures without creating
I am not aware of a way to grant permissions to view the definitions of all stored procedures in a database as you are describing. But if you are willing to let them see slightly more than just the stored procedures, you can grant view definition on either the database or else to schemase within a database. This would look like:
GRANT VIEW DEFINITION On database::test3 TO testuser
Or
GRANT VIEW DEFINITION On schema::DBO TO testuser
Alternately, if you are using source control, you could just give the permissions to view the source control database for the procedures.
As a slight tangent, if you are not using source control for your database objects, I highly recommend it. SSMS 2008 will integrate to a degree with VSS (I also belive TFS, though I have not tested that one). Red Gate is also currently working on a product to integrate SQL Server better with some other source control options. I personally use VSS and Mercurial for different projects and I would recommend either one of them.
