Grant Execute to all SQL Server Stored Procedures

USE DatabaseName

GO

— 1 – db_executestoredprocedures

— 1a – Create role

CREATE ROLE db_executestoredprocedures

GO

— 1b – Grant permissions

GRANT EXECUTE TO db_executestoredprocedures

GO

— 2 – db_selecttablevaluedfunctions

— 2a – Create role

CREATE ROLE db_selecttablevaluedfunctions

GO

— 2 – Create permissions

DECLARE @Function_Name nvarchar(250);

DECLARE @CMDEXEC1 nvarchar(2000);

DECLARE db_cursor CURSOR FOR

SELECT [name]

FROM sys.objects

WHERE Type = ‘TF’

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @Function_Name

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @CMDEXEC1 = ‘GRANT SELECT ON [‘ + @Function_Name + ‘] TO db_selecttablevaluedfunctions;’

 — SELECT @CMDEXEC1

 EXEC(@CMDEXEC1)

 FETCH NEXT FROM db_cursor INTO @Function_Name

END

CLOSE db_cursor   

DEALLOCATE db_cursor

GO

CREATE PROCEDURE dbo.spGrantExectoAllStoredProcs @user sysname

AS

/*—————————————————————————-

— Object Name: spGrantExectoAllStoredProcs

— Author: Edgewood Solutions

— Development Date: 03.19.2007

— Called By: TBD

— Description: Issue GRANT EXEC statement for all stored procedures

— based on the user name that is passed in to this stored procedure

— Project: SQL Server Security

— Database: User defined databases

— Business Process: SQL Server Security

—

—————————————————————————-

— Num | CRF ID | Date Modified | Developer | Description

—————————————————————————-

— 001  | N\A     | 03.15.2007    | Edgewood | Original code for the GRANT

— EXEC process

—

*/ 

SET NOCOUNT ON

— 1 – Variable declarations

DECLARE @CMD1 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 u.[Name], o.[Name]

FROM dbo.sysobjects o

INNER JOIN dbo.sysusers u

ON o.uid = u.uid

WHERE o.Type = ‘P’

AND o.[Name] NOT LIKE ‘dt_%’

— 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 + ‘]’

 — 8 – Execute the string

 — SELECT @CMD1

 EXEC(@CMD1)

— 9 – Decrement @MAXOID

SET @MAXOID = @MAXOID – 1

END

— 10 – Drop the temporary table

DROP TABLE #StoredProcedures

SET NOCOUNT OFF

GO