SQLServerCentral Article

Granting Execute Access to All Stored Procedures to a Given User


Recently, I encountered a situation where I needed to give Execute permission for

a particular user to all of the stored procedures I had created in my database. 

Since there were a few hundred stored procedures, I had not intention of doing so

manually for each procedure, and like any good geek, I wanted to find a way to reuse

my solution in the future. 

So, to that end, I created a quick and dirty T-SQL script that will grant Execute

permission on all stored procedures in the database.  You will need to enter

your own values for the @login value, and of course you can limit the stored procedures

being modified by restricting your results selected into the temp table.  If

you find yourself needing this functionality frequently (I can't imagine why), you

could always create a procedure from this code.

[Brief disclaimer: Please don't e-mail me and tell me that I should use a cursor

for this.  Yes, I know this could also be done with a cursor.  However,

for a variety of reasons too lengthy to list here, I prefer to use temp tables and/or

loops instead of cursors whenever possible.]

Download the code


1 (2)




1 (2)