THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

DYK: Maintenance Plans have Owners

I never knew this until I read a forums post today, but Maintenance Plans have owners in SQL Server. 

Beyond that, there is no way to change who the owner of a Maintenance Plan is in SQL Server Management Studio.  Generally speaking when a DBA leaves a company, their domain user account is disabled in Active Directory, or at least I hope so.  If that user account was used to create a Maintenance Plan, it will still be the owner, and by default the owner of the Maintenance Plan is also the owner of the SQL Agent Jobs that run the plan as well.  The jobs can easily be updated to keep them running, but if the Maintenance Plan ever needs to be updated in the future, when the changes are saved, the owner for the SQL Agent Job is reset back to being the owner of the Maintenance Plan.  A little research online, and I read more than once that you have to recreate the Maintenance Plan with a correct login to get around this, but I also found a blog post which shows how to change the owner of the plan with a simple update to MSDB.

UPDATE msdb.dbo.sysdtspackages90
SET ownersid = 0x01
WHERE name = 'MaintenancePlan'

The code is really simple, but only works for SQL Server 2005.  In SQL Server 2008, a slight change is needed as noted in the workarounds posted on a connect feedback to change the behavior of Job ownership after editing a Maintenance Plan.

UPDATE msdb.dbo.sysssispackages
SET [ownersid] = SUSER_SID('sa')
WHERE [name] = 'MaintenancePlan' 

If you find this interesting, please vote on the feedback as well.

Published Monday, May 04, 2009 2:59 PM by Jonathan Kehayias
Filed under:

Comments

 

Sankar Reddy said:

Jonathan,

Just for completeness, the same issue happens in SSRS too. I had to deal/change the ownership of at-least 3 previous colleagues.

And here is how it can be achieved.

http://blogs.msdn.com/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

May 5, 2009 12:34 AM
 

Jeff Williams said:

I was modifying a maintenance plan the other day and happened to change to name.  When I reviewed the job I saw that the job owner had been changed to my account.

I reviewed the table and saw that indeed - I was now the owner of the maintenance plan.

So, I started up SSMS using ShellRunAs to run as a different user, and changed the name of the maintenance plan.  Checked out the owner - and, the owner is now changed.

Just another way of getting the owner changed to the correct user account.

May 12, 2009 1:27 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement