Maintenance Plan Owner – Back to Basics

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 Server, we can use the following to find that I am now the owner of that maintenance plan we just created.

USE msdb;
GO
DECLARE @ServerMajorVersion DECIMAL(4, 2)
,@SQLProduct VARCHAR(24)
,@SQL VARCHAR(MAX);
SELECT @ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.'
+ PARSENAME(dt.fqn, 3))
FROM ( SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))
) dt ( fqn );
IF OBJECT_ID('tempdb.dbo.#SQLVersions') IS NOT NULL
BEGIN
DROP TABLE #SQLVersions;
END
CREATE TABLE #SQLVersions
(VersionID INT IDENTITY(1,1)
, Product VARCHAR(32)
, ServerMajorVersion DECIMAL(4, 2) --INDEX CI_ServerMajorVer CLUSTERED
);
CREATE CLUSTERED INDEX CI_ServerMajorVer ON#SQLVersions (VersionID);
INSERT INTO#SQLVersions
( Product
, ServerMajorVersion
)
VALUES
( 'SQL Server 2008', 10.00)
,( 'SQL Server 2008R2', 10.50)
,( 'SQL Server 2012', 11.00)
,( 'SQL Server 2014', 12.00)
,( 'SQL Server 2016', 13.00)
,( 'SQL Server 2017', 14.00)
,( 'SQL Server 2019', 15.00)
,( 'SQL Server 2005', 9.00);
SELECT @SQLProduct = Product
FROM #SQLVersions sv
WHERE sv.ServerMajorVersion = @ServerMajorVersion;
SET @SQL = '
SELECTp.name AS PackageName
, '''+ @SQLProduct + ''' AS SQLVersion
, p.createdate
, pf.foldername AS PackageType
, sp.name AS PrincipalName
FROM ' + CASE WHEN @ServerMajorVersion < 10 THEN 'msdb.dbo.sysdtspackages90 p
INNER JOIN dbo.sysdtspackagefolders90 pf
ON p.folderid = pf.folderid'
ELSE 'msdb.dbo.sysssispackages  p
INNER JOIN dbo.sysssispackagefolders pf
ON p.folderid = pf.folderid' END + '
LEFT OUTER JOIN master.sys.server_principals sp
ON p.ownersid = sp.sid
WHERE pf.foldername = ''Maintenance Plans''
ORDER BY PackageName;'
EXECUTE (@SQL);
IF OBJECT_ID('tempdb.dbo.#SQLVersions') IS NOT NULL
BEGIN
DROP TABLE #SQLVersions;
END

Notice that in this query, I delve out to 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. I also am running a dynamic SQL query to support both views dependent on version of SQL Server.  I figured this might be a tad more helpful than the previous version here. This query 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.

The Wrap

In this article I took a rather long route to a simple fix. It’s easy to try each of the steps I showed in this article thinking it will help. It isn’t illogical to try some of those steps. They just don’t work unfortunately. In the end, getting to know the settings in the database and what the errors are really trying to get at is most helpful. Sometimes, it just takes a few more steps to get to the real meaning of the error.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.