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.
Best Answer
As well as granting permissions, you should consider schemas.
So your stored procs are named in the Procs schema
Then you can
GRANT EXECUTE ON SCHEMA::Procs TO RoleWhatever
. All stored procedures created inProcs
then inheritEXECUTE
permissionsRoleWhatever could be a support role in production but a developer role in other environments.