How to allow a user to run a SQL Server Agent Job from a client application.

I wanted to allow users to run a SQL Server agent Job from a client application. Short Answer CALL msdb.[dbo].[sp_start_job] I had a number of errors but the fix was to 1) Add their login as a user to the msdb database 2) Put the user into group: SQLAgentOperatorRole
Long answer I had a stored procedure like below

CREATE PROCEDURE [dbo].[startJob] @JobName nvarchar(255) as
BEGIN
 declare @retval int
 EXEC @retval = msdb..sp_start_job @JobName
 return @retval 
END

And runs OK for me but didnt work for a user. Problem 1 "the execute permission was denied on the object 'sp_start_job' database 'msdb'" To check this permission run this query

SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC
FROM MSDB.SYS.DATABASE_PERMISSIONS DP
JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID
JOIN MSDB.SYS.DATABASE_PRINCIPALS PR
ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'

Results
NAME                 PERMISSION_NAME STATE_DESC
TargetServersRole EXECUTE         DENY
SQLAgentUserRole EXECUTE         GRANT

USE [msdb]
GO
CREATE USER [domain\securitygroup] FOR LOGIN [domain\securitygroup]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'domain\securitygroup'
GO

Then I got

Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('My Job X') does not exist.

This is because the user did not have permission to run someone else's job. They need to be made a member of the SQLAgentOperatorRole to do that.

EXEC sp_addrolemember N'SQLAgentOperatorRole', N'domain\securitygroup'
GO

References: