Post reply

Allow user to view job info fails with access denied

  • October 7, 2009 at 7:49 am

    #226868

    I want to grant a domain account the ability to view job information. I added the account to the SQLAgentOperatorRole role but when they connect to the instance via SSMS they get the error:

    The EXECUTE permission was denied on the object 'xp_instance_regread', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

    What can I do to grant rights to view job info? Do I have to grant rights to the various XPs too?

    SQL Server 2005 64bit, all the latest patches

    Thanks

    JC

  • October 7, 2009 at 7:58 am

    #1062786

    Sounds like you are having the same issue as me. Refer to my post in this same forum - http://www.sqlservercentral.com/Forums/Topic792839-359-1.aspx. In short, a workaround is to give them the necessary permissions in the master database and they should work. The correct fix involves fixing these certificates but I am 100% unsure how to correctly do that (yet).

  • October 7, 2009 at 8:29 am

    #1062808

    Thanks for the link. I am having the exact same experience. I compared the certs and they are the same. I ran your test script and get the same error. I found that by granting the account execute permission and placing it in the operator role everything worked.

    What is very strange is if I connect to an instance with SSMS using the account, I get the error when connecting if the account is in the operator group. If the account is not in the group, I don't get the error when I connect (and SQL Agent does not appear in the list (which does make sense)).

    I hope someone can make sense of this.

    Thanks

  • October 7, 2009 at 7:49 pm

    #1063205

    Check out my post now. I have a script put together that will drop and recreate all the necessary pieces of the certificates and certificate users. There could be a couple of issues causing this, as i learned. On two of our servers, the certificates between msdb and master were different. One one of the servers, they are the same but the certificate user was missing in msdb. This script addresses all of the above.

Viewing 4 posts - 1 through 3 (of 3 total)

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