Late last night, I got the call that no DBA wants to get.
“Our Data Center has crashed”
It appears, both controllers in the VM Ware SAN crashed at the same time bringing every VM server down. Once the controllers were replaced, servers were brought back online. But that’s not the real point of this article, it is just the background of what happened.
SQL Logins
Typically when I install a SQL server, I use a domain Security Group as the Sys Admin. Members of this group include myself, my monitoring service account and domain admins. Because I am a member of this group, I typically remove my individual login. This has not been an issue until last night; well really this morning.
SQL Agent Jobs
I had SQL Agent jobs failing all over the network once my servers were back up and running from the earlier crash and I couldn’t figure out why.
Message
The job failed. The owner () of job SystemDB.Backup-Full does not have server access.
The owner()…does not have server access. Why did it not list the owner? Is there an owner?
I understand the importance of having non-domain accounts “owning” SQL Agent jobs; now more than ever. As much as I try to replace myself as job owner when I edit a job; it just sometimes slips my mind.
Every job owned by my network account was failing across the company. When researching I tried to get a listing of all jobs and their owners using the script below; but not all the jobs were listed. Why weren’t all jobs being listed?
SELECT
j.job_id
,j.name
,l.name
FROM msdb.dbo.sysjobs j
INNER JOIN master.sys.syslogins l
ON j.owner_sid=l.sid
Digging deeper, I remembered that syslogins is actually a view of sys.server_principles. Reading the entries there, my network login was not a principle which made sense because I removed my network login principle. But my login was a job owner; apparently SQL will use the current logged in user as the new owner ever if it is not a principle. I tested this by removing my login from a test server, creating a new job and there she blows!
So, I wanted needed a quick way to change job ownership of all SQL Agent Jobs. So I developed the following script.
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @job_owner VARCHAR(50),@job_name VARCHAR(150),@login_name VARCHAR(150)
DECLARE jobs CURSOR FOR
SELECT
j.job_id
,j.name
,l.name
FROM msdb.dbo.sysjobs j
INNER JOIN master.sys.syslogins l
ON j.owner_sid=l.sid
WHERE l.name <>'sa'
OPEN jobs FETCH NEXT FROM jobs INTO @jobID,@job_name,@login_name
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE @run INT
PRINT 'Changing ' + @job_name + ' owner from ' + @login_name + ' to [sa]'
EXEC @run = msdb.dbo.sp_update_job @job_id=@jobID,@owner_login_name=N'sa'
IF @run=0
BEGIN
PRINT 'Success: ' + @job_name + ' owner was changed to [sa]'
END
ELSE IF @run <> 0
BEGIN
SELECT 'Fail: ' + @job_name + ' owner was not changed'
END
FETCH NEXT FROM jobs INTO @jobID,@job_name,@login_name
END
CLOSE jobs
DEALLOCATE jobs
SELECT
j.name
,j.enabled
,l.name
FROM msdb.dbo.sysjobs j
INNER JOIN master.sys.syslogins l
ON j.owner_sid=l.sid
This script will be added to my “toolbox” for sure. I may set this up as a SQL Agent job itself randomly checking, or maybe a SSIS package using @Variables for Server Names, but for now, I will manually run this on severs that need it.
Lesson Learned!
Padre's SQL Resort 