SQL Agent database role and xps

Question

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]

Viewing 4 posts - 1 through 3 (of 3 total)

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

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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