How to find Job owner for all SQL Server Agent Jobs and change the owner

How to find Job owner for all SQL Server Agent Jobs and change the owner

We got below error from so many servers in my organisation.

Error: "The job failed.  The owner '' of job '' does not have server access."

Causes: Most of the below cases.
1. Logins does not exists
2. Job owner left from the organization
3. Jobs owner disabled state.

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 to use 'service account' or 'sa' as SQL Server Job owner, so you don't have to worry in case owner leave the company.

Fix:

Single job we can fix manually but multiple servers multiple jobs how can we fix this issue, so that we found a way using below script by joining the below tables and query.

master.sys.syslogins--Find the login Name
msdb..sysjobs--Find the jobs Name

Below query will return your server job names and respective job owners.

SELECT SJ.NAME AS JobName

    ,SL.NAME AS OwnerName

FROM msdb..sysjobs SJ

LEFT JOIN master.sys.syslogins SL ON SJ.owner_sid = SL.sid

  

Or

Adding little more stuff for serverName, job which is enabled\disabled state.

SELECT                     @@servername as Server_name,

                                 sj.name, CASE

                                                      WHEN sj.enabled = 1 THEN 'Enable'

                                                      ELSE 'Disable'

                                               END             AS JobStatus,

                                 SUSER_SNAME(sj.owner_sid) AS owner

from                msdb..sysjobs_view sj

left outer join master.sys.syslogins sl on sj.owner_sid = sl.sid

where               SUSER_SNAME(sj.owner_sid) <> 'sa'

or                         SUSER_SNAME(sj.owner_sid)  is null

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

--Please provide the New Owner for The Jobs you like to Change, I am using sa

DECLARE @OWNERName VARCHAR(100)

SET @OWNERName = 'sa'--please change SA or SQL Instance service account

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 SJ.NAME AS JobName
    ,SL.NAME AS OwnerName
FROM msdb..sysjobs SJ
LEFT JOIN master.sys.syslogins SL ON SJ.owner_sid = SL.sid
WHERE L.NAME <> @OWNERName

OPEN Job_Cursor --Open a cursor

FETCH NEXT FROM Job_Cursor  --Fetch Data from cursor

INTO @JobName
    ,@OldJobOwner

WHILE (@@FETCH_STATUS <> - 1)

BEGIN
    EXEC msdb..sp_update_job @job_name         = @JobName

                            ,@owner_login_name = @OWNERName

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

 + @OWNERName

FETCH NEXT
    FROM Job_Cursor
    INTO @JobName
        ,@OldJobOwner
END

CLOSE Job_Cursor -- Close the cursor

DEALLOCATE Job_Cursor --De-allocate the cursor

You can wrap up by creating the stored procedure with @OWNERName parameter and use that stored procedure as a job in every server to change the job owners to 'sa' or 'service account'. 

Just to be safe side please initially test is 'TEST\DEV' server and then move to production.