The Execute Permission was denied on object 'sp_start_job', database 'msdb', schema 'dbo'

The Execute Permission was denied on object 'sp_start_job', database 'msdb', schema 'dbo'

To execute any job in SQL Server Agent you must have the below three database roles on MSDB Database,

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

The user has all the above three db roles on MSDB eventhough that user is getting permission deined error while executing agent jobs.

Use the below query to verify if there are any issues with permissions


use msdb
go
SELECT  OBJECT_NAME(so.object_id) AS obj_Name, spr.name, sdp.permission_name, sdp.state_desc
FROM sys.database_permissions sdp, sys.objects so, sys.database_principals spr
WHERE sdp.major_id = so.object_id AND spr.principal_id = sdp.grantee_principal_id
and so.name = 'sp_start_job'
If no rows returned then we need to execute the below command to assign execute permission on sp_start_job to SQLAgentUserRole

Grant execute on sp_start_job to SQLAgentUserRole

If two rows returned with Grant for SQLAgentUserRole and Deny for TargetServerRole, then

MSDB - Security - Users - Properties - Add Target Server Role to that user
Grant execute on sp_start_job to TargetServerRole

Now execute the job it will be executed by that user.