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  Just incase the article is ever taken down, this is what it said;


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) + '.' +

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) + ''.'' +

exec master.dbo.xp_execresultset @sql,@db

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

I absolutely love these. They came at the lace wigs are good quality, and very soft. Absolutely love these extensions. I just love this hair. Hair came on time, I waited until after hair extensions uk very full, no shedding and it is been installed for 3 weeks now and yes I jus ordered 3 more bundles. Lol. I definitely recommend this company for your bundles ladies. They are soft, hair bundles on the head for human hair wigs and I would continue to recommend them. I have thinner hair so they cover and blend nicely.