Grant a database role in current database with permission to execute a SP in msdb 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.
Microsoft created special roles that allow this sort of thing

"SQL Server Agent Fixed Database Roles"

Granting EXECUTE on sp_start_job won't work, because permissions and the checks made when starting a job are linked to role membership only.

USE msdb
ALTER ROLE SQLAgentOperatorRole ADD MEMBER DR_user;