Maintenance plans–change owner

Problem

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.

 

Solution

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.