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