I've always done some poor man's hack in TextPad to grant EXEC permissions to all stored procedures in a SQL database after restoring from a remote backup or some similar situation. You know, get a list of stored procs then do a regular expression search and replace until I had my Grant Exec script.
grant exec on DNW_GetRecentPosts to me
grant exec on DNW_HomePageData to me
grant exec on DNW_Stats to me
grant exec on DNW_Total_Stats to me
....
So I decided not to do that anymore and came up with this script which seems to work like a charm (until someone tells me how lame it is and how it can be improved on...) Until that happens, here's the script to generate a list of Grant exec statements for all stored procs in a database.
declare proccursor CURSOR
GLOBAL
For
Select name
From sysobjects
Where type = 'P' And Status >0
Order By name
open proccursor
fetch next from proccursor into @procname
while @@fetch_status=0
begin
print 'grant exec on ' + @procname + ' to me'
fetch next from proccursor into @procname
end
close proccursor
deallocate proccursor