Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
