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 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, no default 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 2000 and 2005:

SQL Server 2000

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

SQL Server 2005

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

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 object. In addition, the corresponding user or role would need to be passed into this stored procedure to grant the rights correctly.

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: