Category Archives: SQL Toolbox

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.

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?





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 @job_owner VARCHAR(50),@job_name VARCHAR(150),@login_name VARCHAR(150)







FROM msdb.dbo.sysjobs j

INNER JOIN master.sys.syslogins l

ON j.owner_sid=l.sid

WHERE <>'sa'


OPEN jobs FETCH NEXT FROM jobs INTO @jobID,@job_name,@login_name



    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


        PRINT 'Success: ' + @job_name + ' owner was changed to [sa]'


        ELSE IF @run <> 0


            SELECT 'Fail: ' + @job_name + ' owner was not changed'


    FETCH NEXT FROM jobs INTO @jobID,@job_name,@login_name


CLOSE jobs






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 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.


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”

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code


To Catch A Falling Cactus


Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance