Change Job owner for all SQL Server Agent Jobs - SQL Server Scripts

I was checking my emails and saw below error message from SQL Server monitoring tool.
"The job failed.  The owner '' of job '' does not have server access.",

That means a DBA had created a SQL Server Agent  job with his name as owner. Once he/she will leave the company and account will be removed from AD, the jobs will start failing.

It is good idea to use service account or sa as SQL Server Job owner, so you don't have to worry in case owner leave the company:)


Below Query will return you Job names and their owners.

SELECT J.NAME AS JobName
    ,L.NAME AS OwnerName
FROM msdb..sysjobs J
LEFT JOIN master.sys.syslogins L ON J.owner_sid = L.sid

Now if we would like to update all the job where owner is not sa, we can use below query. You can also modify the query to filter the jobs for which you would like to update owner.

--Provide the New Owner for The Jobs you like to Change, I am using sa
DECLARE @NewOWNERName VARCHAR(100)

SET @NewOWNERName = 'sa'

DECLARE @OldJobOwner VARCHAR(100)
DECLARE @JobName VARCHAR(1000)

DECLARE Job_Cursor CURSOR
FOR
--Change your Query as per requirements, I am selecting all Job where owner<>sa
SELECT J.NAME AS JobName
    ,L.NAME AS OwnerName
FROM msdb..sysjobs J
LEFT JOIN master.sys.syslogins L ON J.owner_sid = L.sid
WHERE L.NAME <> @NewOWNERName

OPEN Job_Cursor

FETCH NEXT
FROM Job_Cursor
INTO @JobName
    ,@OldJobOwner

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
    EXEC msdb..sp_update_job @job_name = @JobName
        ,@owner_login_name = @NewOWNERName

    PRINT 'Ownerd Change for ' + @JobName + 'Job from ' + @OldJobOwner + ' to '

 + @NewOWNERName

    FETCH NEXT
    FROM Job_Cursor
    INTO @JobName
        ,@OldJobOwner
END

CLOSE Job_Cursor

DEALLOCATE Job_Cursor

Just to be on safe side, test the script in Development environment before run in production.