x

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

more ▼

asked Mar 23, 2010 at 04:37 PM in Default

Schommer gravatar image

Schommer
44 1 1 1

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.
Mar 23, 2010 at 04:38 PM Schommer
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?
Mar 23, 2010 at 04:39 PM Schommer
(comments are locked)
10|1200 characters needed characters left

4 answers: sort oldest

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

more ▼

answered Mar 25, 2010 at 04:53 PM

Schommer gravatar image

Schommer
44 1 1 1

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 23, 2010 at 05:11 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
If you rename the Maintenance Plan, that'll change the ownership information.
more ▼

answered Oct 18, 2010 at 01:16 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left

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]')`

more ▼

answered Oct 19, 2010 at 06:36 AM

jforsberg gravatar image

jforsberg
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x88
x86
x37

asked: Mar 23, 2010 at 04:37 PM

Seen: 5022 times

Last Updated: Mar 23, 2010 at 04:37 PM