Grant a database role in current database with permission to execute a SP in msdb
2
votes
3
answers
8547
views
I have created a role in my current SQL Server 2012 database with
db_datareader
, db_datawriter
, and execute
permissions. A procedure in the current db calls sp_start_job
in msdb.
How can I grant access to my database role to execute the procedure in msdb?
I tried executing the procedure in my current database which calls sp_start_job
as owner and the user who is a member the user defined role is still not able to execute the procedure.
I have made the server role public
for the login, and mapped it to msdb database, but I'm not able to grant permission. The command I'm trying to execute is:
GRANT EXECUTE ON OBJECT::[msdb].[dbo].[sp_start_job] TO [db_executor]
When I execute from msdb, the error I get is:
>Cannot find the user 'db_executor', because it does not exist or you do not have permission'.
When I execute from the database in which the user defined role, db_executor
is created, it throws this error:
>You can only grant or revoke permissions on objects in the current database.
I don't want to add users to SQLAgentOperatorRole
. The idea is to group all users in one role within the app's database and the members of this role should be able to run a procedure in the database which calls sp_start_job
.
Asked by JKay
(35 rep)
Mar 30, 2017, 06:22 AM
Last activity: Jul 12, 2019, 02:49 AM
Last activity: Jul 12, 2019, 02:49 AM