You are here:Home » SQL Server » The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’. - Error in SQL Server

The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’. - Error in SQL Server

For a security reason, I have created some users to have a permit to execute scheduled jobs only. So, given some required permissions in the msdb database, Even though users are not able to execute scheduled jobs in SQL Server. Below are the agents database roles are given for msdb database.


Even have above database roles in msdb database to execute scheduled jobs, users received following error when tried to run a job.
The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.
After finding a solution with online reference, found a script to check required permissions of users. Below is a script used to check for same.
USE msdb
GO

SELECT 
 PR.NAME, 
 DP.PERMISSION_NAME, 
 DP.STATE_DESC
FROM SYS.DATABASE_PERMISSIONS DP
 JOIN MSDB.SYS.OBJECTS O 
  ON DP.MAJOR_ID = O.OBJECT_ID
JOIN SYS.DATABASE_PRINCIPALS PR
 ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'
GO
Finally, I saw EXECUTE permission was denied on SQLAgentUserRole and TargetServersRole roles over sp_start_job system stored procedure in msdb database. I granted it and it works finally. I would like you to share your experience of such relevant errors.