7

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.

  • 2
    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 Nov 10 '16 at 15:22
11

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
5

None by default.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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