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.
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' GOFinally, 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.
Comments
Post a Comment