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