question

Schommer avatar image
Schommer asked

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-agentjobmaintenance
2 comments
10 |1200

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

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 Likes 0 ·
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 Likes 0 ·
Schommer avatar image
Schommer answered

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

10 |1200

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

Matt Whitfield avatar image
Matt Whitfield answered

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

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

ThomasRushton avatar image
ThomasRushton answered
If you rename the Maintenance Plan, that'll change the ownership information.
10 |1200

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

jforsberg avatar image
jforsberg answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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