I like it when bloggers have the solution easy to find at the top and then explain in a little more detail below.  I’ll try following that as much as possible.

Recently, I was asked to blanket all user stored procedures.  The practicality of this may be limited, but a useful trick and I thought I’d share along with breaking it down.

SQL 2000

USE [DataBaseName]
DECLARE Proc_Cursor CURSOR
FOR SELECT NAME FROM sysobjects WHERE objectproperty( id, N'IsMSShipped' ) = 0
AND objectproperty( id, N'IsProcedure' ) = 1
OPEN Proc_Cursor
DECLARE @Name VARCHAR(500)
DECLARE @Query VARCHAR(500)
FETCH NEXT FROM Proc_Cursor INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Query = 'GRANT EXEC ON ' + @Name + ' TO [CustomRole]'
  EXEC(@Query)
  FETCH NEXT FROM Proc_Cursor INTO @Name;
END
CLOSE Proc_Cursor;
DEALLOCATE Proc_Cursor;

SQL 2005/SQL 2008/2008 R2

USE [DataBaseName]
DECLARE Proc_Cursor CURSOR
FOR SELECT NAME FROM sys.objects WHERE type = 'P' AND is_ms_shipped = '0'
OPEN Proc_Cursor
DECLARE @Name VARCHAR(500)
DECLARE @Query VARCHAR(500)
FETCH NEXT FROM Proc_Cursor INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Query = 'GRANT EXEC ON ' + @Name + ' TO [CustomRole]'
  EXEC(@Query)
  FETCH NEXT FROM Proc_Cursor INTO @Name;
END
CLOSE Proc_Cursor;
DEALLOCATE Proc_Cursor

I’ll break the above methods down for in the 2008 version to simplify reading.

First, we create a cursor of all objects that are stored procedures that are user defined.

DECLARE Proc_Cursor CURSOR
FOR SELECT NAME FROM sys.objects WHERE type = 'P' AND is_ms_shipped = '0'

Next, open the cursor and work each stroed proc captured in the above select statement with a WHILE loop, creating dynamic SQL.  The dynamic SQL is needed since you can’t execute a GRANT command with a variable out right:

OPEN Proc_Cursor
DECLARE @Name VARCHAR(500)
DECLARE @Query VARCHAR(500)
FETCH NEXT FROM Proc_Cursor INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Query = 'GRANT EXEC ON ' + @Name + ' TO [CustomRole]'

Execute our new Query each time we find a new stored proc in our cursor then fetch the next:

  EXEC(@Query)
  FETCH NEXT FROM Proc_Cursor INTO @Name;
END

When we’re done, make sure to close your cursor and deallocate its resources:

CLOSE Proc_Cursor;
DEALLOCATE Proc_Cursor;

Happy granting…