Read only access to stored procedure contents

Question

Is it possible to set up SQL Server to give developers read-only access to the contents of stored procedures on our production database?

Answer 1

You can grant them the VIEW DEFINITION privilege to those procs.
See here for what this privilege does.

You can apply VIEW DEFINITION at different scopes:

• Server
• Database
• Schema
• Individual entities (e.g. a proc, a function, a view)

You can also use a query to generate a script for many procs.
So if you have a user Bob:

SELECT N'GRANT VIEW DEFINITION ON '     + QUOTENAME(SPECIFIC_SCHEMA)     + N'.'     + QUOTENAME(SPECIFIC_NAME)     + N' TO Bob;' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'; 

that will give you something like this, which you can then run:

GRANT VIEW DEFINITION ON [dbo].[aspnet_RegisterSchemaVersion] TO Bob; GRANT VIEW DEFINITION ON [dbo].[aspnet_CheckSchemaVersion] TO Bob; GRANT VIEW DEFINITION ON [dbo].[aspnet_Applications_CreateApplication] TO Bob; ... 

Answer 2

Example of granting VIEW DEFINITION at the Schema scope/level for dbo.

GRANT VIEW DEFINITION ON schema::[dbo] TO Bob; 

MSDN Article