Maintenance plans–change owner


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
owner=SQL Systembruker’
WHERE owner LIKE ‘Feil bruker’

On MSSQL 2008R2:

UPDATE msdb.dbo.sysssispackages
ownersid=suser_sid(NEW USER)
WHERE ownersid = suser_sid(OLD USER)

or just set sa as the owner of everything:

UPDATE msdb.dbo.sysssispackages
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
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
WHERE owner_sid != 0x01

If it doesn’t seem to work as expected, restart the agent service.