Home Forums SQL Server 2005 SQL Server 2005 Security The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. RE: The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

  • --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