Maintenance Plan Owner

We all inherit things from time to time through our profession.  Sometimes we inherit some good things, sometimes we inherit some things that are not so good.  Other times we inherit some things that are just plan annoying.  Yet other times, we inherit things that may be annoying and we probably just haven’t discovered them yet.

Dizzying, I know.

Inheritance

Have you ever taken over a server that had several maintenance plans on it?  Have you ever really checked who the owner of those plans is?  Or, maybe you had a failing job relating to one of these maintenance plans and you changed the job owner, but did you really fix the root cause?  That could be one of those things that you inherited that could be annoying but you just don’t know it yet.

Step by Step

No this is not New Kids on the Block (I think I just threw up in my mouth thinking that).

Let’s create a generic maintenance plan and see what happens.

The first thing we do is navigate to Maintenance Plans under the Management menu in Management Studio.  Right Click the Maintenance Plan folder and select New Maintenance Plan… from the context menu.  This will prompt us with the following dialog box.

In this box, we can type a name for this Maintenance Plan that is to be created.  I chose MaintPlanOwner, since that is the topic of this article.

After clicking ok on this dialog box, you will be presented with a blank canvas with which to design your maintenance plan.  I have chose a simple task for the purposes of this article.

I will create a subplan named Statistics and add the Update Statistics task to the canvas.

You can see this illustrated to the left.  I chose to update the statistics on all databases and left all other options as the default option – for simplicity of this article.

At this point, the only thing left to do is to save this Maintenance Plan.  Once the plan is saved, then we can move on to the next step – some fun with TSQL.

Fun with TSQL

This is the stage of the article where we get to play with TSQL and investigate at a high level the Maintenance Plan we just created.

Within the msdb database, we have some system tables that store information about SSIS packages, DTS packages, and Maintenance Plans.  We will be investigating from a SQL 2008 and SQL 2005 standpoint (it changed in 2005 and then again in 2008).

In SQL 2005, we can query the sysdtspackages90 and sysdtspackagefolders90 to gain insight into who owns these Maintenance Plans.  In SQL 2008 and up, we can query sysssispackages and sysssispackagefolders to gain the same insight.  These system tables are within the msdb database.

In SQL 2005, we can use the following to find that I am now the owner of that maintenance plan we just created.

USE msdb;
GO
SELECT p.name AS PackageName,p.createdate,pf.foldername AS PackageType,sp.name AS PrincipalName
FROM msdb.dbo.sysdtspackages90  p
INNER JOIN dbo.sysdtspackagefolders90 pf
ON p.folderid = pf.folderid
INNER JOIN master.sys.server_principals sp
ON p.ownersid = sp.sid
WHERE pf.foldername = 'Maintenance Plans';

For 2008, a slight modification yields the following query that returns the same results.

USE msdb;
GO
SELECT p.name AS PackageName,p.createdate,pf.foldername AS PackageType,sp.name AS PrincipalName
FROM msdb.dbo.sysssispackages  p
INNER JOIN dbo.sysssispackagefolders pf
ON p.folderid = pf.folderid
INNER JOIN master.sys.server_principals sp
ON p.ownersid = sp.sid
WHERE pf.foldername = 'Maintenance Plans';

Notice that in both queries, I delve out in the sys.server_principals catalog view.  I did this to retrieve the name of the owner of the package that was found in the sysdtspackages90 and sysssispackages  tables respective to version of SQL Server.  These queries would yield the following result set for that new Maintenance Plan that was just created.

Caveat

Let’s assume that this package is scheduled via a SQL Agent job on a production server.  I then get moved to a different department and no longer have permissions on this particular production server.  The job will start failing due to the principal not having access.  One fix would be to change the owner of the job.

That will work.  However, there is a problem with that fix.  As soon as somebody opens and saves the Maintenance Plan, the owner of the job will revert back to the owner of the Maintenance Plan.  When that happens, then the job will fail again.

A permanent fix is needed.  The permanent fix is to change the owner of the Maintenance Plan.  The following will change the owner to ‘sa’ for both SQL 2005 and SQL 2008 (and up).

SQL 2005

USE msdb;
GO
UPDATE p
SET ownersid = 0x01
FROM msdb.dbo.sysdtspackages90  p
INNER JOIN dbo.sysdtspackagefolders90 pf
ON p.folderid = pf.folderid
WHERE pf.foldername = 'Maintenance Plans';

SQL 2008

USE msdb;
GO
UPDATE p
SET ownersid = 0x01
From msdb.dbo.sysssispackages  p
INNER JOIN dbo.sysssispackagefolders pf
ON p.folderid = pf.folderid
WHERE pf.foldername = 'Maintenance Plans';

Now if you run the code used earlier to investigate, you will find that the owner has indeed changed.  The results of that query should be similar to the following.

There you have it.  No more hair tugging over something as benign as the owner of a Maintenance Plan.  This is one of those things that should be looked at as soon as you inherit a new server.