Post reply

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

  • March 6, 2013 at 3:42 pm

    #1594199

    ddg8885 (10/20/2010)

    I searched the error for a while and saw a lot of things about certificates differing between msdb and master. But I checked the certificates and they were identical.

    I compared my server to others, and noticed that the ##MS_AgentSigningCertificate## user was missing from the master database. Running the following query worked for me:

    USE MASTER

    CREATE USER [##MS_AgentSigningCertificate##] FOR LOGIN [##MS_AgentSigningCertificate##]

    GO

    GRANT EXECUTE TO [##MS_AgentSigningCertificate##]

    go

    I know this is old but wanted to thank you for posting a solution. I think I had a few things going on. Here is what I did:

    1. (forgot to mention) First I followed the steps in KB article http://support.microsoft.com/kb/2000274

    2. Added ##MS_AgentSigningCertificate## to master and granted EXEC

    3. I also had a disabled guest User account in msdb so had to also issue this:

    USE msdb;

    GRANT CONNECT TO guest;

    After that database listings and Agent Job listings started working in Object Explorer.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • May 6, 2013 at 4:59 pm

    #1612819

    Tony Fountain (10/9/2009)

    Ok, for what it's worth... I found out I can simply rerun the latest service pack exe and it will also correct this problem... go figure - so much simpler!

    Tony,

    How did you rerun the service pack? Don't you get "no update" error?

  • January 10, 2018 at 8:02 am

    #1975289

    --https://support.microsoft.com/de-de/help/2000274/how-to-fix-permission-issues-when-moving-msdb-database-between-different-instances
    --
    use msdb
    go
    -- Backup the Agent certificate from the remote server to a file
    BACKUP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = 'd:\ServerInstall\MS_AgentSigningCertificate.remote_server.cer'
    go
    use master
    go
    -- re-create the agent certificate on master
    -- Note: Because we are making these changes using a regular user and not as part of setup, the name
    -- cannot include the ## token.
    -- Creating a regular certificate in this case should be the equivalent as we only need it to derive a SID

    CREATE CERTIFICATE [MS_AgentSigningCertificate.remote_server] FROM FILE = 'd:\Serverinstall\MS_AgentSigningCertificate.remote_server.cer'
    go
    -- Recreate the user mapped to the cert and grant the same permissions that the regular certificate needs.
    CREATE USER [MS_AgentSigningCertificate.remote_server] FROM CERTIFICATE [MS_AgentSigningCertificate.remote_server]
    go
    GRANT EXECUTE TO [MS_AgentSigningCertificate.remote_server]
    go

Viewing 3 posts - 16 through 18 (of 18 total)

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