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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  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

• clare.xia

  SSCrazy

  Points: 2569

  More actions

  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?

• MasterOfQueries

  Grasshopper

  Points: 14

  More actions

  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

• stefan.quinn

  Grasshopper

  Points: 18

  More actions

  May 21, 2021 at 4:53 pm

  #3886421

  I'm using a Azure SQL managed instance so couldn't tinker with the certs even though they looked different.

  I was granting permissions to an AD group but got around this error by creating an individual login for the user in question (yes not ideal but I only have 1 user who needs this permission) and granted the following:

  USE [master]

  GO

  CREATE USER [user@yourdomain.com] FOR LOGIN [user@yourdomain.com]

  go

  GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [user@yourdomain.com]

  GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [user@yourdomain.com]

  GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [user@yourdomain.com]

  GRANT EXECUTE ON master.dbo.sp_helplogins TO [user@yourdomain.com]

  GO

  USE [msdb]

  go

  CREATE USER [user@yourdomain.com] FOR LOGIN [user@yourdomain.com]

  GO

  ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [user@yourdomain.com]

  ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [user@yourdomain.com]

  ALTER ROLE [SQLAgentUserRole] ADD MEMBER [user@yourdomain.com]

  ALTER ROLE [db_datareader] ADD MEMBER [user@yourdomain.com]--may not be needed

  ALTER ROLE [db_datawriter] ADD MEMBER [user@yourdomain.com]--may not be needed

  GO