Change the Owner of a Maintenace Plan

Question

Change the Owner of a Maintenace Plan

Andy sql

SSCrazy Eights

Points: 9483
More actions
October 12, 2007 at 8:39 am

#153888

The owner of a new Maintenance Plan is always the user who created the plan. How can this owner be changed? I may be being stupid, but I cannot see how to change the owner using Management Studio.
I know I could possibly change it programmatically (eg. the view sysmaintplan_plans in msdb), but surely there is an official, GUI-way to change the owner?
Thanks,
Andy

Viewing 12 posts - 1 through 11 (of 11 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

It can be changed in Visual Studio. Open some Project. Click Project ->Add Existent Package, specify the server. Select a path under Package Path as SSIS->Maintenance Plans. Use the button to make this selection. Select your package. The path is enetered as:

/Maintenance Plans/MyPlanName

When the package name will be added to your project, double-click on the package name and open Properties Window if it is not open as View->Properties window.

In Properties: There is a CreatorName property with could be changed.

What is interesting, that this property is called CreatorName here while it is called Owner in Management Studio.

Then you can save the package back to SQL Server.

The problem with this approach that you will have to modify the package in SSIS from now on and not in Management Studio in 2005 without service packs

Yelena

Regards,Yelena Varsha

Andy sql SSCrazy Eights Points: 9483 More actions · Answer 2

Thanks for the response. I can successfully add the Maintenance Plan to an existing Visual Studio project, just as you described.

Sorry to quiz you further, but if the (production) server does not have any development tools installed, and I modify the Maintenance Plan in Visual Studio, will I still be able to modify the Maintenace Plan in Management Studio (under Management, Maintenace Plans) on the Server? Or will I need additional dev tools (which are not installed on the server)?

On a different tack, this is a lot of hoops to jump through just to change the owner. Are there any other solutions people know of?

Thanks,

Andy

Steve Jones - SSC Editor SSC Guru Points: 734512 More actions · Answer 3

I haven't seen another way to do this. It doesn't appear that SSMS does it, and even a change in the table

update sysdbmaintplans

set owner = 'joe'

doesn't seem to change things.

Is there a reason you need to change this?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

I think that they must have meant Creator not the Owner. In Visual Studio it is Creator, not Owner. And as with the Creator, it should not be changing?

I think I read a post here that somebody just re-saved the Maintenance Plan under another name and then renamed to change the owner. I will search.

Regards,Yelena Varsha

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

I found it:

Change Owner in Database Maintenance Plan:

Change Owner in Database Maintenance Plan When connected in Management Studio as SA simply rename the Maintenance Plan. This changes the owner to sa and rebuilds all the jobs with sa as the owner. ...

I found this reference using the SEARCH function here on the website, but can not post a link because it opens the REPLY window, not the original post.

Regards,Yelena Varsha

Andy sql SSCrazy Eights Points: 9483 More actions · Answer 6

If the owner is in reality the creator, then no, I do not need to change it. And I could understand why it cannot be changed.

Are there any implications? For example, I am a contractor at a company, and I have created Maintenance Plans for them. The Plans are currently owned by my Active Directory user. When I leave, my user will be disabled (or maybe deleted). This would leave orphaned Maintenance Plans.

I suppose my question is, does this matter? From a neatness point of view, it is not great, but I think I can live with that.

Thanks,

Andy

george sibbald SSC Guru Points: 104210 More actions · Answer 7

you could just go through and change the owner of the jobs the maintenance plan creates in SQLAgent, effect will be the same.

george

---------------------------------------------------------------------

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 8

I usually wind up changing the owner of the jobs created by the maintenance plan in SQLAgent. I find it to be a big pain in the neck. Every time I change a maintenance plan I have to make sure I change the job owner as well. There should be an option when creating a maintenance plan to choose the owner if different from the creator.

george sibbald SSC Guru Points: 104210 More actions · Answer 9

Ronda London (10/16/2007)
I usually wind up changing the owner of the jobs created by the maintenance plan in SQLAgent. I find it to be a big pain in the neck. Every time I change a maintenance plan I have to make sure I change the job owner as well. There should be an option when creating a maintenance plan to choose the owner if different from the creator.

Agreed it is a pain and one of the biggest gotchas for jobs failing on first run

---------------------------------------------------------------------

Andy sql SSCrazy Eights Points: 9483 More actions · Answer 10

Completely agree, manually changing the credentials a Job runs under is almost always necessary. And a standard gotcha.

With regards the Owner/Creator of a Maintenance Plan - if the existing name is just a text label, then I am happy to leave well alone. If it is tied to a SID, which might well become orphaned, I am not so happy. Still, I guess any sys admin could always come along and re-save the Plan under a different name.

Thanks for your help, expecially Yelena.

Regards,

Andy

lloydbk Ten Centuries Points: 1347 More actions · Answer 11

Great thread:-)

I've been living with this issue for a long time. Original problem was I changed ownership of sqlagent jobs to sa when they started failing intermitantly due to password expiring. That worked, but then much later when it came to modify the maintenance plan it always came up with an "unable to save plan" error, however most of the time the changes would be saved anyway after saying "no" to the changes a second time. This lead to issues when major changes were made to the maintenance plan database lists and the sqlagent job would fail with meaningless errors. To get around this, I had to remove all databases from the plan and put them all back again. I have just gone through the exercise of logging in to Management Studio as sa and renaming the maintenance plans. That does the trick perfectly. Such a simple solution, well done !!!!