GRANT on all SP's?

[Hansje]

So how can I GRANT EXECUTE on all stored procedures in a database, without using a cursor? Is this possible? Sofar I have not been able to get this done.
A step further: is it possible to GRANT EXECUTE on every stored procedure in every userdb, again without using a cursor?

 

[clapag22]

select 'grant exec on ' + name + ' to joeblow
GO'
from sysobjects
where type = 'p'

Take the results, paste them in a new query window and run.

This will work for all the SP's in a specific DB, if you want to do all DB's, you'll need to get creative using the sysdatabases table in the Master DB and then running the above query for each DB.

 

[LFCfan]

Also check out FAQ1834107 (and the web) for sp_MSforeachdb.
This system stored proc will execute your statement against every single db, so you would have to include an if statement to make sure the particular db is a user one