When creating a SQL Server maintenance plan, the owner of the plan is set to the user logged in when the plan is created. If another user makes changes, the owner does not change. Sometimes it may become necessary to change the owner (e.g. if the owner's account is being disabled or removed). You can manually change the owner through the SQL Server Agent's Job Activity Monitor but changing it there is only temporary: changes to a maintenance plan will reset the owner back to the original one. Changing the owner from within the maintenance plan is not possible through the GUI, but there is a way to make the change through T-SQL statements.

The following queries need to be run against the msdb system database. When running from the CLI, first change to that database:

USE msdb
GO

You can list all maintenance plans and their owners (the owner column):

SELECT * FROM dbo.sysmaintplan_plans

The table above is read-only in SQL Server 2005 or later. In order to change the owner, you need to perform the change against another table. Depending on the version of SQL Server, this can either be:

• SQL Server 2005: dbo.sysdtspackages90
• SQL Server 2008 or newer: dbo.sysssispackages

To make the change, you can use a variety of queries (or use inline editing):

To change the owner for a specific plan (named "SomeMaintenancePlan"):

UPDATE msdb.dbo.sysssispackages
SET [ownersid] = SUSER_SID('the-new-user')
WHERE [name] = 'SomeMaintenancePlan'

If you have to change an account which is owner of several maintenance plans (e.g. useful in case of a disabled account), you could do something like this:

UPDATE msdb.dbo.sysssispackages
SET [ownersid] = SUSER_SID('the-new-user')
WHERE [ownersid] = SUSER_SID('the-old-user')

Note that changing the ownership does not change the ownership of the actual jobs (in SQL Server Agent). For this to apply to the jobs, you need to either:

• Change the owner on the job in SQL Server Agent's Job Activity Monitor.
• Open the maintenance plan, and save it again. This will update the owner in the job as well.