Did you ever have one of those déjà vu moments when you are working in SQL Server and you swore you already addressed an issue? This has happened to all of us, and working in SQL Server every day, I’ve certainly had my fair share of SQL déjà vu.

One of those moments came after I received an alert that one of my maintenance plan jobs failed on a client’s server, and I noticed that they had the job owner set to an employee who doesn’t work there anymore. I had come to find out that in between the last successful run of the job and it failing, the client removed the user from their Active Directory. Depending on the edition of SQL Server, the output of the error will look similar to the error in the image below, but all the errors basically state that the job failed because of an invalid job owner.

I did what I’m supposed to do as a DBA and set the job owner to ‘sa.’ I informed the client that the job was fixed, and there shouldn’t be any more problems. Right…?

Wrong!!!

A couple of weeks later, I received another alert for a failed job. I logged into SQL Server to look at the error, and the job failed because the owner reverted back to the former employee. Am I going crazy? I remember I definitely changed the job owner to ‘sa.’

After doing some research, I found that there is a bug on SQL Server 2005 to SQL Server 2008R2 that changes a job owner of a maintenance plan job back to the maintenance plan owner that is stored in MSDB every time the maintenance plan is changed and saved.

In order to ensure that you never run into this issue again, you need to change the job owner as well as the maintenance plan owner to ‘sa.’ I’ve included the syntax below for each edition of SQL Server. All you have to do is run the SELECT statement to find the name of your maintenance plan and replace the string in the WHERE clause of the UPDATE with the name of your maintenance plan.

SQL Server 2005
USE MSDB
GO
SELECT *
FROM sysdtspackages90
---------------------------------------
UPDATE sysdtspackages90
SET ownersid = 0x01
WHERE name = 'NAME OF MAINTENANCE PLAN'
SQL Server 2008 & SQL Server 2008R2
USE MSDB
GO
SELECT *
FROM sysssispackages
---------------------------------------
UPDATE sysssispackages
SET ownersid = 0x01
WHERE name = 'NAME OF MAINTENANCE PLAN'

Thank you for reading.