Read only access to stored procedure contents

问题:

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

回答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

回答2:

Example of doing at the schema scope level for dbo.

grant view definition on schema :: [dbo] to BOB

MSDN

回答3:

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

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

回答4:

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')

回答5:

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.