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.
