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