SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Stored Procedure Definitions and Permissions

I wrote a post a while back that showed how you can grant execute permission ‘carte blanche’ for a database role in SQL Server. You can read that post here. This post is going to build on that concept of using database roles for groups of users and allocation permissions to the role. I recently had a situation where a tester wanted permission, for themselves and the rest of the testing team, to look at the definition of all the stored procedures on a specific database, strangely enough for testing purposes. I thought for a while on how best to grant this permission, I did not want to grant the VIEW DEFINITION permission at the server level or even the database level. I just wanted to grant for all the store procedures that existed in the test database at that time. This is the solution I came up with:
Create a database role in the specific database called db_viewspdef
CREATE ROLE [db_viewspdef]

I then added the tester windows group to that role:
USE [AdventureWorks]
EXEC sp_addrolemember N'db_viewspdef', N'DOM\TesterGroup'

My next task was to get a list of all the Stored Procedures in the database, for this I used the following query against sys.objects:
FROM    sys.objects
WHERE   type = 'P'

I then thought about concatenating some code around the result set to allow SQL to generate the code for me, so I used:
SELECT  'GRANT VIEW DEFINITION  ON ' + s.name+'.'+d.name + ' TO [db_viewspdef]'
FROM    sys.objects d
INNER JOIN sys.schemas s ON d.schema_id =s.schema_id
WHERE   type = 'P'
ORDER BY d.name

As you can see I joined sys,objects to sys.schemas to get the schema qualified name for all the stored procedures in the Adventureworks database. I changed the output the query results to text and copied the results from the results pane to a new query window. I fired the query, permission to view the definition of each stored procedure currently in the database was granted.



Posted by npcrwill8 on 18 March 2011

Very clean, simple solution that's well scripted, I think !

Posted by Gethyn Ellis on 18 March 2011

Thanks, I hope it helps . Thanks for taking the time to leave a comment.

Posted by Harmeet Singh on 27 March 2011

Good article. Thanks

Posted by oryza.anggara on 21 May 2014

Best Solution ive find so far. many thanks

Leave a Comment

Please register or log in to leave a comment.