Single query to grant Read/Write/View permissions to all stored procs


–For SQL Server 2005 and above
 
CREATE PROCEDURE spGrantReadWriteViewToAllStoredProcs @user sysname
AS

SET NOCOUNT ON

— 1 – Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @CMD2 varchar(8000)
DECLARE @CMD3 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)

— 2 – Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

— 3 – Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE ‘dt_%’
AND ROUTINE_TYPE = ‘PROCEDURE’

— 4 – Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

— 5 – WHILE loop
WHILE @MAXOID > 0
BEGIN

— 6 – Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID

— 7 – Build the string
SELECT @CMD1 = ‘GRANT EXEC ON ‘ + ‘[‘ + @OwnerName + ‘]’ + ‘.’ + ‘[‘ + @ObjectName + ‘]’ + ‘ TO ‘ + @user
SELECT @CMD2 = ‘GRANT ALTER ON ‘ + ‘[‘ + @OwnerName + ‘]’ + ‘.’ + ‘[‘ + @ObjectName + ‘]’ + ‘ TO ‘ + @user
SELECT @CMD3 = ‘GRANT VIEW DEFINITION ON ‘ + ‘[‘ + @OwnerName + ‘]’ + ‘.’ + ‘[‘ + @ObjectName + ‘]’ + ‘ TO ‘ + @user

— 8 – Execute the string
EXEC(@CMD1)
EXEC(@CMD2)
EXEC(@CMD3)

— 9 – Decrement @MAXOID
SET @MAXOID = @MAXOID – 1
END

— 10 – Drop the temporary table
DROP TABLE #StoredProcedures

SET NOCOUNT OFF
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: