Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


SQL Server Agent Job Ownership
Published: Aug 07, 2015
I’ve come across a few issues recently with regard to the ownership of SQL Server Agent jobs which caused some scheduled tasks to fail and therefore critical maintenance tasks weren’t carried out. Therefore I thought I would share my thoughts on Job Ownership.

When I investigated the issues I was witnessing SQL Server provided the same reasons behind each failure… that SQL Server could not validate the job owner’s login.

The cause of this was simple… the logins being used as owners for the job were Windows Domain logins and the server had just been moved to a new domain.

SQL Server Agent will run each job under the job owner’s credentials and this is the exact case I use when stating that I believe all scheduled jobs should be owned by a dedicated SQL Server login.

The main problem with a Windows login is that, firstly, not only could you have a scenario in which passwords expire and the login is disabled, but also that Active Directory servers could go offline or network connectivity could drop. All of these would render your windows login unusable and cause your scheduled jobs to fail.

If you use a SQL Server login instead then SQL itself can authenticate the user and even if your network or Active Directory server happens to go down, your jobs (for example nightly backups and maintenance) can happily carry on functioning so that when your network connectivity etc returns your server’s health is as expected.

Generally I use a dedicated login for jobs to which no-one has access (outside of any DBA team) but which has the minimum access required to run the jobs to which it’s assigned. In a larger environment you could use a series of logins with differing security levels in order to maintain an even more secure system.

This isn’t a hard and fast rule, but I personally would definitely consider it best practice to avoid Windows logins where possible.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.