Find and update the owner of SQL Job, Maintenance Plan

When we update Maintenance plan, ownership of the job will be changed back to plan owner (In my case it was inactive account resulting job failure).
This is a known bug in SQL Server 2005 and SQL Server 2008. (Ref: https://connect.microsoft.com/SQLServer/feedback/details/295846/job-owner-reverts-to-previous-owner-when-scheduled-maintenance-plan-is-edited).

This is obviously frustrating at times, I want to share the work around script.

1. Update the owner of the MaintenancePlan to avoid such occurrences in future

-- -- on SQL Server 2008 
--view plans ownership
select SUSER_SNAME([ownersid]) as OwnerName, *
from msdb.dbo.sysssispackages
where [name] like 'MaintenancePlan%'
 
--update plans ownership
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa') -- 0x01
where [name] like 'MaintenancePlan%' -- Replace with actual Plan Names

-- -- on SQL Server 2008
--view plans ownership
select SUSER_SNAME([ownersid]) as OwnerName, *
from msdb.dbo.sysssispackages90
where [name] like 'MaintenancePlan%'
 
--update plans ownership
update msdb.dbo.sysssispackages90
set [ownersid] = suser_sid('sa') -- 0x01
where [name] like 'MaintenancePlan%'  -- Replace with actual Plan Names

2. I also had a situation where one of the domain account was planned for retirement, had to update the ownership of all the jobs from old account to new account. (I have decided to change it with sa, this will avoid job failure when the Server is not able to authenticate domain account). Following is the script to see all the owners of SQL jobs and next query will generate update statement to change the owner

-- All jobs and the owners of the job 
   select @@SERVERNAME as ServerName, l.name as JobOwner, j.name as JobName, j.enabled as isjobEnabled, j.date_created
   , j.date_modified, job_id from msdb.dbo.sysjobs j (nolock)
   JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid
      
-- Script to generate owner update statement 
   select 'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(job_id AS VARCHAR(40))+ ''', @owner_login_name=N''sa''' as UpdateQuery,
   @@SERVERNAME as ServerName, l.name as JobOwner, j.name as JobName, j.enabled as isjobEnabled, j.date_created
   , j.date_modified, job_id from msdb.dbo.sysjobs j (nolock)
   JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid
      where (l.name like '%%') OR (l.name like '%DomainName%') -- Update the Criteria
Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , , . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s