Read only access to stored procedure contents

Is it possible to set up SQL Server to give developers read-only access to the c开发者_如何转开发ontents of stored procedures on our production database?

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

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

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

MSDN Article

I needed to grant access to a domain account.. syntax for this was

grant view definition on schema :: [dbo] to "domain\BOB"

DB Name --> Security --> Users --> Right Click User name --> Properties --> select Securables --> Click Search --> select All Objects of type --> Check Stored Procedures --> Check View Definition --> OK.

While i know this is late in response to the question, but for those people that have come here looking for an answer (just as I did a few minutes ago) in at least SQL management studio 2014 you can edit the user account and edit the permissions in the SECURABLES area and tick all the boxes in VIEW that you want them to have access to.

This does the above without having to run scripts.

My variant for all db procedures:

select 'GRANT VIEW DEFINITION ON [' + schema_name(schema_id) + '].[' + name +
       '] TO ' + '[domain\username]'
  from sys.all_objects
 where type_desc = 'SQL_STORED_PROCEDURE'
   and schema_id <> schema_id('sys')