Sometimes you might want to change the owner of a job connected to a maintenance plan. For instance, the current owner could be a deleted account, something that will cause the jobs to fail with not so funny error messages regarding permissions or missing accounts. It is possible to change the job owner using TSQL or Management Studio, but each time the plan is updated, the owner is overwritten by the owner of the maintenance plan. To change this, you have to use TSQL AFAIK.
NB: Running this is dangerous if you don’t know what you are doing! The msdb.dbo.ssispackages table contains system objects as well as your manually created plans, and could also contain SSIS packages as the name indicates. Always take a backup of MSDB before making changes.
Changing the maintenance plan owner on MSSQL 2000:
UPDATE msdb.dbo.sysdbmaintplans SET owner=’SQL Systembruker’ WHERE owner LIKE ‘Feil bruker’
On MSSQL 2008R2:
UPDATE msdb.dbo.sysssispackages SET ownersid=suser_sid(’NEW USER’) WHERE ownersid = suser_sid(’OLD USER’)
or just set sa as the owner of everything:
UPDATE msdb.dbo.sysssispackages SET ownersid=0x01 WHERE ownersid != 0x01
Afterwards, either save the plan to update job ownership or run the following to change the job owner as well.
UPDATE msdb.dbo.sysjobs SET owner_sid=suser_sid(’SQL Systembruker’) WHERE suser_sname(owner_sid) LIKE ‘Feil bruker’
or just set sa as the owner of everything here as well:
UPDATE msdb.dbo.sysjobs SET owner_sid=0x01 WHERE owner_sid != 0x01
If it doesn’t seem to work as expected, restart the agent service.