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

Grant them VIEW DEFINITION privilege to those procs, see here http://msdn.microsoft.com/en-us/library/ms175808.aspx

There is

• Server scope

• Database scope

• Schema scope

• Individual entities

you can also use a query to generate a script. so if you have a user Bob

select 'GRANT VIEW DEFINITION ON ' + quotename(specific_schema) 
+ '.' + quotename(specific_name)
+ ' 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