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:
Posts
