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:
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: