Read only access to stored procedure contents
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