You are here:Home » SQL Server » The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys' - SQL Server

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

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!