Overview

Whilst SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will). This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005

SQL2000

A common answer to the question posed by the title of this article is to run a query such as the one below in Query Analyzer and copy and paste the results into a query window and execute them. The query uses the INFORMATION_SCHEMA views to generate a list of GRANT statements for each procedure in the database.

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO <insert_username>' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0	

However, what we can do is shortcut the copy and paste step by using a system extended stored procedure called xp_execresultset (Note that after SQL2000 Service Pack 3, xp_execresultset is no longer an extended stored procedure but simply a wrapper for the system stored procedure sp_execresultset) This extended stored procedure allows you to pass in a SQL query that generates a result set that it will then execute as long as the result of the query is a legitimate SQL batch. Thus we can take the query above and pass it to xp_execresultset and it will generate a resultset containing GRANT statements for each procedure in the database which will then be executed thus neatly avoiding the need to copy and paste. I have created a stored procedure called sp_grantexec (click to follow link to code) that takes a user name or role as a parameter and grants execute permissions on all stored procedures in the current database to wrap this up and make it easier to use. An example of using xp_execresultset is shown below.

declare @sql nvarchar(4000)
declare @db  sysname ; set @db = DB_NAME()
declare @u   sysname ; set @u = QUOTENAME('<insert_username>')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' + 
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

exec master.dbo.xp_execresultset @sql,@db

SQL2005

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor