Granting privileges to stored procedures

I've just encountered a user who wanted to run a stored procedure or 3 against as SQL Server database on which he ws not the owner of the stored procedures. This was a bit of a surprise because although added to the database after creation he is in there as dbo. Never mind needed a solution sooner rather than later.

It's simple really in that Securables can be manually editted for that user, unfortunately there are a large number of procedures potentially required - in fact it is a 3rd party product so we won't have a full list of required procedures until we've run the process repeatedly weeding out each issue as it arises.

The answer :

select 'Grant Execute on ' + name + ' to "username"'
from sysobjects where xtype in ('P')

This works nicely by outputting a full list of statements that can be copied from the output and executed in the query window in SSMS. The important feature here is the username - this is the full username ie if you are using Windows accounts it will be DOMAIN\user enclosed in the shown speech marks to ensure spaces are handled properly.