Sunday, 16 December 2012

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.