patternsqlMajor
SQL Server : which role grants permission to execute all stored procedures?
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
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
e.g.
For the default
For a new schema:
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
GOFor a new schema:
CREATE SCHEMA mySchema
GO
CREATE ROLE role_exec_mySchema
GO
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
GOCode Snippets
CREATE ROLE role_exec_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo
GOCREATE SCHEMA mySchema
GO
CREATE ROLE role_exec_mySchema
GO
GRANT EXECUTE ON SCHEMA::mySchema to role_exec_mySchema
GOContext
StackExchange Database Administrators Q#154841, answer score: 21
Revisions (0)
No revisions yet.