Assigning execute permissions to Stored procedure in bulk on sql server 2000


Phewf- that is a lengthy title! 

The ongoing battle with the corporate machine to invest a bit of cash on some new tech is ongoing- as a result our primary RDBMS is still MS Sql 2000!  I recently had a fairly large database built by a third party which we need to host- i restored from the backup they supplied and setup a new user with no permissions and started gradually building access only to what was neede- but with 923 stored procedures to grant execute permission to, this suddenly looked like a total chore.

Luckily a bit of googling by my colleague Paul, turned up this little nugget from sqldbatips.com.  Just incase the article is ever taken down, this is what it said;

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
	

  1. No comments yet.
(will not be published)