SQL Server : which role grants permission to execute all stored procedures?
If you are using schemas other than the default dbo
schema, create a database role per schema and grant EXECUTE
on the schema to the role.
e.g.
For the default dbo
schema:
CREATE ROLE role_exec_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo
GO
For a new schema:
CREATE SCHEMA mySchema
GO
CREATE ROLE role_exec_mySchema
GO
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
GO
None by default.
Create a new role and grant execute to it. This should cover stored procs created in the future as well.