Changes in Management Plan overwrites owner of SQL Job


In one of the production SQL Servers, I created a new database.  After which I added that database into the existing Maintenance Plan, meant for Daily backup (This maintenance plan was created long back by another DBA, with his account).  Next day, I noticed that the backup job had failed.  While investigating the issue, I found that the job owner was changed from sa account to old DBA account (Old DBA moved out of organisation long back).



When you change anything on a maintenance plan created by another person, the original creator will be stored as the owner of the job every time you make a change in the SSIS package. This happens because SQL will try to keep the owner of both objects in sync. But the owner of the package is not exposed, so you have to update MSDB by hand to change it to any other thing – lets say “sa” for example.



Code to update the package owner.  Please refer the below link as well.


--view all packages
select * from msdb.dbo.sysssispackages
--update plans ownership
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] like 'MaintenancePlan%'
Note: Replace the name and owner account in the script as per your requirement.
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: