SQL Login / Agent Jobs Ownership SNAFU

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!

server_principle

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!

Advertisements

Padre’s Toolbox

I have been working with SQL long enough that I have built my personal “toolbox” for SQL related tools that I use on a regular basis. They range from FREE to minimal cost (by that I mean mostly under $100; yeah, I know I am cheap).

You can find my tools here, Padre’s Toolbox and I hope to keep it updated as I test drive and use more and more different SQL programs.  For now, I will briefly introduce my favorite tools (in no particular order).  In the future, I hope to write a post on how and why I am using these tools.

SQL CoPilot

Since my initial review of this software, Richard Fryar has even further enhanced his product to include Query Plan analysis. 

SQL Server First-Aid Kit

This is from Brent Ozar Unlimited.  Enough Said! You not only get their excellent diagnostic scripts, but e-books, whitepapers, and posters Oh My!

DMV Diagnostic Queries

Here is the most recent iteration of Glenn Berry’s fantastic information queries.  Glenn has been providing these since SQL 2005 and if SQL stores the information, these queries will find it!

Idera Free Tools

A collection of free SQL tools, I find my self using these more and more for “spot checking” issues or problems.

Terminals

Not technically a SQL Tools, but as a DBA we have to sometimes manage the OS itself.  This tool is crucial for me to be able to remote into multiple servers without having to minimize RDP session to switch between servers.

SQL Sentry Plan Explorer

My newest tool in my arsenal, is one that quite frankly scared the hell out of me at first.  Just like Cache Plans in general. But I am mudding through the learning process and I am coming to rely more and more on this program.  I haven’t purchased the “Pro” version yet, so I am still evaluating the usefulness of this program and see if I can justify the cost to my “powers-that-be”