Post reply

SQLAgentUserRole Anomoly

  • June 16, 2008 at 4:37 pm

    #208457

    We have a vendor who needs to review job properties for numerous jobs. I granted their SQL login (yuck) user rights to the msdb database and then made the user the owner of these jobs. Finally I granted that user SQLAgentUserRole role rights in msdb.

    They receive the following error when clicking on the Jobs folder:

    Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

    The odd thing is that this is the development cluster. The prod cluster is set up identically and there is no issue there. The user rights are identical and the job ownership is identical. The instances are both patched to 9.0.3042.

    Any suggestions that may spur a resolution are appreciated!

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • July 25, 2008 at 9:05 pm

    #848746

    Have you found an answer to this. I have the same problem with a system. I have checked against another system with the same setup and it is all right. What could be wrong and is there a specific place to look. I have spent a few hours comparing to no avail

    Thanks,

    René

  • July 30, 2008 at 12:00 pm

    #850749

    No resolution - still open issue.

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • July 31, 2008 at 12:32 pm

    #851469

    Try this:

    grant execute on master.dbo.xp_sqlagent_enum_jobs to {user};

    grant execute on master.dbo.xp_sqlagent_is_starting to {user};

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • September 25, 2008 at 1:14 pm

    #877291

    Thanks a lot. It worked. Sorry I did not get back to before now. I have been very busy.

    René

  • September 26, 2008 at 6:43 am

    #877616

    Glad to hear it.

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • August 16, 2011 at 2:32 am

    #1368973

    +1 & thanks.

  • April 3, 2012 at 7:08 pm

    #1468596

    Hey, Tim!

    This fixed an issue I was having. Needed a user in one database to run a sproc that kicked off a job.

    Thanks for contributing.

    Ryan Malcom

  • January 15, 2015 at 7:33 pm

    #1770618

    Thanks, that's solve my problem to 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply