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.

  • 3
    if all your stored procs are in the same schema you could grant execute on the schema. GRANT EXECUTE ON SCHEMA::[dbo] TO [login]. Other than that I believe db_owner but this would give all sorts of access
    – Bob Klimes
    Commented Nov 10, 2016 at 15:22

2 Answers 2


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
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo

For a new schema:

CREATE ROLE role_exec_mySchema
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
  • 3
    Then exec sp_addrolemember N'role_exec_mySchema', N'{theDBUserName}'. :-)
    – ΩmegaMan
    Commented Nov 25, 2019 at 19:56
  • 3
    The preferred way of adding users/members to a role is through the ALTER ROLE Foo ADD MEMBER Bar statements. sp_addrolemember is flagged as maintance and may be removed in the future. As per microsofts documentation.
    – D Kramer
    Commented Aug 4, 2020 at 12:05

None by default.

Create a new role and grant execute to it. This should cover stored procs created in the future as well.

  • 3
    what about sysadmin?
    – johnDanger
    Commented Apr 29, 2021 at 22:58
  • 2
    ... and db_owner? Commented May 3, 2021 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.