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.