How to Change Maintenance Plan owner in SQL server 2005.

Yesterday one of my colleagues modified the Maintenance plan, where that Maintenance plan performs

Backup and cleanup tasks for user databases (The jobs were running without any issues before the Maintenance plan modify).

Here is the small story-

We are getting the disk space alerts for one of our servers and to resolve this we plan to change the retention period of cleanup tasks for 3 days to some Hours(>48Hours). So my colleague modified the Maintenance Plan for cleanup task & saved the maintenance plan.

But today when I was in work received the alert that Job has failed which was created via Maintenance plan (the one which was modified yesterday).

Job failure information as mentioned below-

What happened the Maintenance plan was created by one of our colleagues and he moved to different

Account & his current accesses revoked by Security team as per our project policy.

Hence yesterday the maintenance plan modified by other colleague ->by default the Maintenance plan/Package owner has reflected to Job owner because of modification, hence the job was failed with the above reason.

To fix permanently we need to modify the Maintenance plan/Package owner –so I decided to put

Maintenance plan Owner as our service account and I have completed with the below  T-Sql

--You can use  the table of MSDB to get  who is the owner by taking  the information of Ownersid

Table name is as mentioned below

select * from dbo.sysdtspackages90

so using above you can use suser_name to get the owner for the maintenance plan/Package..

SELECT

name, description,suser_sname(ownersid),isencrypted

FROM

msdb.dbo.sysdtspackages90

--Now we have to update the owner to an appropriate domain account. Either the service account or any other account is Ok for you.

(Before performing below step –take MSDB Backup not required but best practice).

UPDATE

msdb.dbo.sysdtspackages90

SET

OWNERSID = SUSER_SID('YOUR_DOMAIN_NAME\ACCOUNT_NAME') --------to which account you are going to change

WHERE

OWNERSID = SUSER_SID('YOUR_DOMAIN_NAME\OLD_ACCOUNT') ----------The old account name ---i,e the job which is failing with this account.

after executing you will get output as 1(rows) affected.

Verify once again to check Maintenance plan owner changed or not, After this I have executed the job

It completed without any issues.

(If you need you change the job owner to SA).