Friday, February 24, 2012

EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

No comments:

Post a Comment