Grant Execute to all SQL Server Stored Procedures

Problem

With SQL Server 2000 no default server, database or application role was available to be able to execute all stored procedures.  With SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 has this changed with all of the new security features?  If not, what options do I have to grant execute rights to the needed database roles?

Solution

Unfortunately, with all of the security changes in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, no system role is available to execute all stored procedures in a given database.  This is the same behavior as with SQL Server 2000.  As such, let's take a look at the needed code for SQL Server 2008 R2, SQL Server 2008, SQL Server 2005 and SQL Server 2000.

Grant Execute to All Stored Procedures in SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005

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

Grant Execute to All Stored Procedures in SQL Server 2000

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

The code above can be used when a new group needs to be granted rights to all stored procedures or you are working through a migration when rights need to be granted.  Based on your naming convention or creation date, the query used in both sets of code can be modify to include the needed objects. 

With all of this being said, the best approach to address this need is to explicitly grant execute rights to stored procedures as the stored procedures are created and migrated to the production environment.  Unfortunately, if you are inheriting a SQL Server environment, you may not be that lucky.  Then the code above can come in handy to streamline the process.

Since this tip references granting stored procedures, the value of stored procedure based access to SQL Server data cannot be overlooked.  As such, stored procedure based access to SQL Server from your front end applications offers the following benefits:

• Security based on the object that can be assigned rights with all business rules incorporated
• No direct access to tables or views
• Ability to call the stored procedure from any piece of code (ASP.NET, VB.NET, C#, CFMX, Job, etc.) to have a consistent set of rules executed
• Change a single piece of code to streamline the code migration process

source collected from mssqltips.com