HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

SQL Server : which role grants permission to execute all stored procedures?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedproceduresserversqlallpermissionrolegrantswhichexecute

Problem

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.

Solution

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

Code Snippets

CREATE ROLE role_exec_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo
GO
CREATE SCHEMA mySchema
GO
CREATE ROLE role_exec_mySchema
GO
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
GO

Context

StackExchange Database Administrators Q#154841, answer score: 21

Revisions (0)

No revisions yet.