The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresour

Recently while working with one query to get schedule jobs 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 following query for same,

1. 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 in mssqlsystemresource database for sys schema.

Solution :

For the solution just need to assign execute permission on dbo.xp_sqlagent_enum_jobs in master database to user under the query to be run. So 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 enjoyed this post.

You can read my some earlier posts,

• Adding/Modifying files and filegroup in Database
• Moving table or indexes on another filegroup

Stay tuned for further post!

Tags: Administration,DBA