Which database role membership grants permission to execute all existing stored procedures in SQL Server 2012?
I tried adding a user to each of them and am still unable to execute a stored procedure. I don't want to grant EXECUTE for each stored procedure separately, I want to add the user to a role and he be able to execute any of them.
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 
exec sp_addrolemember N'role_exec_mySchema', N'{theDBUserName}'. :-) ALTER ROLE Foo ADD MEMBER Bar statements. sp_addrolemember is flagged as maintance and may be removed in the future. As per microsofts documentation. None by default.
Create a new role and grant execute to it. This should cover stored procs created in the future as well.
