Technical Article

Change Ownership of a Maintenance Plan

,

I experienced problems where I had created maintenance plans under one ID but then wanted to run the scheduled jobs that were created under a different ID, when I updated the owner of the jobs under SQL Agent all ran OK. When ever I changed anything in the maintenance plan it republished the jobs under the ID of the original creator. The choice was update the owner of all the jobs every time I made changes or update the owner of the maintenance plans so that the jobs would end up with the correct owner for running. I had seen various suggestions about deleting the jobs and recreating them but I had already spent a lot of time creating them. Another script for SQL 2005 showed how to change ownership but that didn't work on SQL 2008. With a bit looking a simple modification enabled this script to work against SQL 2008.

Run the script to change the owner of a maintenance plan in SQL 2008. The SID in the example (0x01) corresponds to the SA account, you can use the SID of any account you wish to change ownership to. Be sure to update the WHERE clause with the actual name of the maintenance plan you wish to alter.

 

UPDATE msdb.dbo.sysssispackages
SET [ownersid]=0x01
WHERE [name]='YourMaintPlanName'

Rate

4.88 (8)

You rated this post out of 5. Change rating

Share

Share

Rate

4.88 (8)

You rated this post out of 5. Change rating