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

Posted on August 14, 2015, in SQL, SQL Toolbox and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

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

rhondastephens

To Catch A Falling Cactus

SQLSwimmer

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

%d bloggers like this: