SQL Job owner changes back to original owner after changing a Maintenance Plan

Question

question

Schommer asked Mar 23, '10

SQL Job owner changes back to original owner after changing a Maintenance Plan

When saving a change to the maintenance plan the SQL Agent job owner changes back to the original owner of the job. We found out that the job reverts back after we tried to set the job up to run under a proxy account and then made a change to the maintenance plan.

I have posted this question here: Maintenance plans and proxy accounts http://www.sqlservercentral.com/Forums/Topic842203-146-1.aspx?Update=1

And I have followed the recommendation here: The Job whose owner kept coming back...... http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx

Besides manually resetting the owner of the job back, I can't get the new job owner to stay. This is a 32-bit SQL 2005 Developer Box on SP3 (build: 9.0.4035).

Additionally: MyDomain\DevSQL is not a login under SSMS, Security, Logins on my server

sql-agent job maintenance

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Schommer commented · Mar 23, 2010 at 04:38 PM

I already posted this on Experts Exchange and someone suggested: "All you need to do is give permissions to whichever account you want to run this job to do so without changing the job owner, that's all." I asked where these permissions were and they never responded.

0 ·

Schommer commented · Mar 23, 2010 at 04:39 PM

Here was my reply to the "Expert": So if I have a proxy account under SSMS, Security, Credentials named "Dev_Proxy" that has an Identity of "MyDomain\DevSQL" then where do I assign the "permissions" you are referring to?

0 ·

Answer 1 · 2010-03-25T16:53:56Z

Schommer answered Mar 25, '10

I added the domain user as a principal, assigned it in the proxy and everything worked.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-23T17:11:51Z

Matt Whitfield answered Mar 23, '10

I don't know the agent as well as other areas of the system - but is it possible that you could create a profiler trace of what is executed when you alter the job? I would imagine that under the hood it will be calling the SQL Server agent job stored procedures - but if we can find out which ones are being called, then we may be able to find out why the owner is reverting, and then suggest possible work-arounds.

Also, it really does sound like a bug to me - this may or may not be the relevant item on microsoft connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295846&wa=wsignin1.0

Unfortunately, I can't tell you if it is the right connect item or not, because it seems like connect is having an epic fail moment right now. Maybe we should post a connect item about... oh... hang on...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-10-18T13:16:28Z

ThomasRushton answered Oct 18, '10

If you rename the Maintenance Plan, that'll change the ownership information.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-10-19T06:36:50Z

jforsberg answered Oct 19, '10

Check this out. http://dbaspot.com/forums/sqlserver-server/356339-change-maintenance-plan-owner.html Here's a script listed in that forum post that helped me out. SELECT name, description,suser_sname(ownersid),* FROM msdb.dbo.sysdtspackages90 UPDATE msdb.dbo.sysdtspackages90 SET OWNERSID = SUSER_SID('[Domain]\[Generic Package Owner]') WHERE OWNERSID = SUSER_SID('[Domain]\[Old Package Owner]')`

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question