SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent database role and xps


SQL Agent database role and xps

Author
Message
dhammer3407
dhammer3407
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 103
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
AlexSQLForums
AlexSQLForums
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1918 Visits: 2269
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


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
SQLHammer
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 225
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

Derik Hammer
@SQLHammer
www.sqlhammer.com
SQLHammer
SQLHammer
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 225
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search