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]
GO

I then added the tester windows group to that role:

USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_viewspdef', N'DOM\TesterGroup'
GO

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:

SELECT  *
FROM    sys.objects
WHERE   type = 'P'
ORDER BY name

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.