SQL Agent database role and xps

• dhammer3407

  SSC Enthusiast

  Points: 174

  More actions

  August 6, 2012 at 11:59 am

  #259711

  Hello,

  I recently performed a server migration and I'm left with some SQL Agent issues. I restored an msdb from one server to another, I ran sp_help_revlogin on the original box and applied the logins to the new server.

  When I did this the SQL Agent seemed 100% fine because I'm a sysadmin. So I can use SSMS to do everything I need without issue. When a member of a security group that has SQLAgentReader access tried to open the job activity monitor, though, she received a EXECUTE PERMISSIONS DENIED on 'xp_sqlagent_enum_jobs'. I granted the execute permissions and sure enough she next needed execute access to xp_sqlagent_is_starting.

  Granting her individual execute permissions is not a satisfactory solution because this one security group is not the only one I need to ensure works. I need to be able to use the SQL Agent database roles as designed.

  I have a script to create this login and all associated permissions (well tested and is a known good) but the problem does not go away when the login is dropped and created.

  I have read about msdb ownership issues and this is not the case. The owner was sa and just to be doubly sure I ran the change command to sa anyways.

  I have read about the master key being an issue but in all cases when I have read this it is complaining about sysadmins receiving these errors which I do not have that problem.

  I would very much appreciate anyone's assistance here.

  Thank you,

  Derik Hammer

  www.sqlhammer.com[/url]

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  August 6, 2012 at 1:41 pm

  #1522276

  SQLHammer (8/6/2012)

  ---

  Hello,

  I recently performed a server migration and I'm left with some SQL Agent issues. I restored an msdb from one server to another, I ran sp_help_revlogin on the original box and applied the logins to the new server.

  When I did this the SQL Agent seemed 100% fine because I'm a sysadmin. So I can use SSMS to do everything I need without issue. When a member of a security group that has SQLAgentReader access tried to open the job activity monitor, though, she received a EXECUTE PERMISSIONS DENIED on 'xp_sqlagent_enum_jobs'. I granted the execute permissions and sure enough she next needed execute access to xp_sqlagent_is_starting.

  Granting her individual execute permissions is not a satisfactory solution because this one security group is not the only one I need to ensure works. I need to be able to use the SQL Agent database roles as designed.

  I have a script to create this login and all associated permissions (well tested and is a known good) but the problem does not go away when the login is dropped and created.

  I have read about msdb ownership issues and this is not the case. The owner was sa and just to be doubly sure I ran the change command to sa anyways.

  I have read about the master key being an issue but in all cases when I have read this it is complaining about sysadmins receiving these errors which I do not have that problem.

  I would very much appreciate anyone's assistance here.

  Thank you,

  Derik Hammer

  www.sqlhammer.com[/url]

  Do you have identical records when you run this?

  SELECT 'master', *

  FROM master.sys.certificates

  WHERE name = '##MS_AgentSigningCertificate##'

  UNION

  SELECT 'msdb', *

  FROM msdb.sys.certificates

  WHERE name = '##MS_AgentSigningCertificate##'

  ORDER BY 1

  if they are different follow this resolution:

  http://support.microsoft.com/kb/2000274

  Alex S

• SQLHammer

  Right there with Babe

  Points: 729

  More actions

  August 7, 2012 at 7:13 am

  #1522564

  They are not identical the cert_serial_number, sid, string_sid, expiry_date, start_date, and thumbprint are different.

  I will look into that link you sent me and let you know how it goes.

  Thank you,

  Derik Hammer

  www.sqlhammer.com[/url]

  Derik Hammer
  @SQLHammer
  www.sqlhammer.com

• SQLHammer

  Right there with Babe

  Points: 729

  More actions

  August 9, 2012 at 12:16 pm

  #1524023

  I attempted the fix that was suggested but unfortunately it did not solve my problem.

  I used this script:

  use msdb

  go

  -- Backup the Agent certificate from the remote server to a file

  BACKUP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = '\\server\Shared\Backups\MS_AgentSigningCertificate.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] FROM FILE = '\\server\Shared\Backups\MS_AgentSigningCertificate.cer'

  go

  -- Recreate the user mapped to the cert and grant the same permissions that the regular certificate needs.

  CREATE USER [MS_AgentSigningCertificate] FROM CERTIFICATE [MS_AgentSigningCertificate]

  go

  GRANT EXECUTE TO [MS_AgentSigningCertificate]

  go

  What I am failing to understand is how I would get the SQL Agent to use the [MS_AgentSigningCertificate] user instead of the still available [##MS_AgentSigningCertificate##] user.

  Derik Hammer
  @SQLHammer
  www.sqlhammer.com