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.

Answer 1

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.