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.]