Granting execute permission on all stored procedures in a certain database

Question

As the title suggests, I need to grant the execute permission on every stored procedure in a database. We've moved from test to production and have less control over the production database.. and all of the imported stored procedures got zero permissions right now. An interesting thing to know is if there is any way to make sure all of the imported stored procedures get execute permissions from the beginning?

Thanks for any help.

Solution 1

Generate the statements, then copy them and paste into query window to run them

select 'grant execute on ' + 
    QuoteName(specific_schema) + '.' +
    QuoteName(specific_name) + ' to someone'
from information_schema.routines
where routine_type='PROCEDURE'

Solution 2

As well as granting permissions, you should consider schemas.

So your stored procs are named in the Procs schema

• Procs.DoStuff
• Procs.DoMoreStuff
• Procs.WriteStuff

Then you can GRANT EXECUTE ON SCHEMA::Procs TO RoleWhatever. All stored procedures created in Procs then inherit EXECUTE permissions

RoleWhatever could be a support role in production but a developer role in other environments.

Solution 3

You have to grant the execute permission on the schema and repeat if you've got multiple schemas

grant  execute on schema :: yourschema to username