This runs beautifully with the Central Management Servers

I fixed all maintenance plans and jobs on 20+ servers in 1 run 🙂

--get the owners of the maintenance plans

IF @@VERSION LIKE '%SQL SERVER 2008%'--SELECT @@VERSION

BEGIN

SELECT NAME

,description

,SUSER_SNAME(ownersid)

--,*

FROM msdb.dbo.sysssispackages-- sql 2008

WHERE SUSER_SNAME(ownersid) <> 'sa'

END

ELSE

BEGIN

SELECT name

,description

,SUSER_SNAME(ownersid)

FROM msdb.dbo.sysdtspackages90-- sql 2005

END

--get the owners of the maintenance plans

IF @@VERSION LIKE '%SQL SERVER 2008%'--SELECT @@VERSION

BEGIN

UPDATE msdb.dbo.sysssispackages

SET ownersid = SUSER_SID('sa')

WHERE SUSER_SNAME(ownersid) IN ('jerryhung')

--AND [name] = 'MaintenancePlanNameHere' ;

END

ELSE

BEGIN

UPDATE msdb.dbo.sysdtspackages90

SET ownersid = SUSER_SID('sa')

WHERE SUSER_SNAME(ownersid) IN ('jerryhung')

--AND [name] = 'MaintenancePlanNameHere' ;

END