Database maintenance plans are one of the most important utilities in SQL Server, which help novice DBAs in configuring backup and optimization plans. Most of the DBAs that use these maintenance plans configure them to take full backup, transaction log backup , rebuilding indexes and reorganizing indexes.

Whenever a maintenance plan is created by DBAs then the DBA who creates that maintenance plan becomes the owner of the plan. Every maintenance plan will create a SQL job and the DBA will also be the default owner of the SQL Server job.

There had been various incidents in when the DBA sees the job history and finds that the SQL job has started failing due to an error stating that the job owner doesn’t has access to the server. One of the reasons why it happens with the maintenance plan job is because after changing the job or maintenance plan configuration, the new DBA forget to change the job owner.

Let’s take an example where you have a SQL Server DBA who is currently working in your organization. This DBA installs one server ‘A’  and creates a maintenance plan for SQL backups . Then one day this DBA quits the organization and the organization hires a new SQL Server DBA (DBA2) to look after the SQL servers. One day while doing a health analysis of the server, DBA2 found that the server A backup job is failing. The new DBA looks into the issue and resolve the issue by opening the maintenance plan and saving it with a new configurations.  The next day DBA2 comes in and does the health analysis of the server. DBA2 finds that the SQL backup job has failed, stating the job owner does not have server access.

DBA2 looks into the error and sees the job owner of the SQL job and finds that the job owner was the previous DBA. He wonders why the old DBA's account is reflected here. To resolve this problem permanently, DBA2 needs to change the maintenance plan owner to SA or the account that needs to run the job. By doing this we don't have to manually change the job owner every time we make a change in job or maintenance plan, thereby eliminating the failure of the job due to negligence.

SQL Server 2000

To change the maintenance plan owner in SQL Server 2000, we need to follow the steps below that will make sure the next time any modification is made, the instance will use the job owner as the new maintenance plan owner.

Step 1

Execute the below statement to retrieve the current plan names and owners of your maintenance plans.

Use MSDB
go
select plan_name,owner
 from dbo.sysdbmaintplans

Step 2

To change the maintenance plan owner, execute this code. Replace LoginName with the name that was returned from Step 1.

Use MSDB
go
update dbo.sysdbmaintplans
 set owner ='sa'
 where owner ='LoginName'

If you have multiple plan owners, you will need to execute the statement above for each loginname in step 1.

The above two steps will set the maintenance plan owner to SA.

SQL Server 2005 and later

When changing the maintenance plan owner in SQL 2005/2008/2008R2, we need to follow these steps that will assist in changing the maintenance plan owner details.

Step 1

Execute the below statement to  check the current details of the entire maintenance plan configured on SQL Server.

Use MSDB
go
select *
 from dbo.sysmaintplan_plans

The results from this are shown below.

Step 2

To change the maintenance plan owner in SQL 2005, we need to execute the below statement.

Use MSDB
go
UPDATE msdb.dbo.sysdtspackages90
 SET OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')
 WHERE OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')

Step 3

If we have a SQL Server 2008/208 R2, then we need to execute the below statement so that the plan owner can be changed.

Use MSDB
go
update msdb.dbo.sysssispackages
 set [ownersid] = suser_sid('sa')
 where [name] = 'MaintenancePlan'

The results are shown here:

I sincerely hope that this has been of some value to you and it’s actually a good and simple way to change maintenance plan owner details . This is a good article for everyone who has faced this issue in the past and a good learning experience for people who may face this issue in future. Please remember that my company does not allow me to participate in forums and websites or blogs on company machine and thus there can be a delay in response.