How to GRANT EXEC Permission for all the Stored Procedures in specified DB

Suppose we have big list of stored procedures in the database. And DBAs are asked to grant EXEC permission to all the stored procedures in the database for the specific user. Then this article would be much helpful for them.

It can be done by anyone of below mentioned two ways.

Option 1

This code loops through all stored procedures in the database and grants execute privileges to a database user.

drop procedure dbo.spGrantExectoAllStoredProcs

Go

CREATE PROCEDURE dbo.spGrantExectoAllStoredProcs @user sysname

AS

/*---------------------------------------------------------------------------

-- Description: Issue GRANT EXEC statement for all stored procedures

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

*/

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 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 + ']'

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

/*

Execution Part

--------------

Exec dbo.spGrantExectoAllStoredProcs [~svc_cm_ombud]

*/

Option 2

We can also create a server role and grant it EXECUTE privileges on all stored procedures. This solution is much more elegant, plus we don't have to rerun our script if we add more procedures:

-- create custom database role
CREATE ROLE db_executor

-- grant EXECUTE permission

GRANT EXECUTE TO db_executor

-- add security account to the role

exec sp_addrolemember 'db_executor', 'YourSecurityAccount'

Note - security account can be a database user, database role, a Windows login or Windows group.