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

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.

Another way to list out running scheduled jobs - SQL Server by serverku

We have been discussing the way to get scheduled jobs which are executing. Also wanted to tell you that this way is just similar to an earlier post with the same subject, but this an another alternate way. Please read my earlier relative posts for the same. You may aware of the dbo. sp_help_job which also help us they get the same information which you may read my one of the recent post.

Here I am sharing one more way to get the same information, where need to use sp_get_composite_job_info system object from msdb database and pass @execution_status parameter and value should be 1 for executing state.

USE msdb
GO

EXEC dbo.sp_get_composite_job_info @execution_status=1;

This will output all jobs which are currently executing. But when you run EXEC msdb.dbo.sp_get_composite_job_info without any parameter, it will give all the jobs and status as well. And the state values are following,

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

You can pass any parameter values to get the jobs having that state. We all know about the query to get enabled\disabled scheduled jobs using db.sysjobs system table and having enabled = 1, but the same information we can know using msdb.dbo.sp_get_composite_job_info as following,

USE msdb
GO

-- Enabled jobs
EXEC dbo.sp_get_composite_job_info @enabled = 1;

-- Disabled jobs
EXEC dbo.sp_get_composite_job_info @enabled = 0; 

There are some other parameters which also can be used with this object like job_id, job_type etc. You may aware of this sp and may be used. Please suggest any other way using this object.

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys' - SQL Server by serverku

Recently, while working with one query to get schedule job status, I used xp_sqlagent_enum_jobs object which gives details of state values of all jobs in msdb database and this is undocumented object. But received an error while running the following query for same,

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa';

Error :

Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

As per error, says it does not have execute permission on mssqlsystemresource database for sys schema.

Solution :
For the solution just need to assign execute permission on dbo.xp_sqlagent_enum_jobs in the master database to user under the query to be run. So the following query needs to be run against master database,

USE master
GO
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO test;

Hope this help you if you will receive such an error and you may enjoy this post. Stay tuned for further posts!