| Granting execute permissions to all stored procedures in a database |
| Author |
Jasper Smith |
Hits |
1971 |
| Create Date |
23-05-2004 |
Last Updated |
24-07-2004 |
| Versions |
SQL2000, SQL2005 |
|
|
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
|
|
articles
