SQL Server Tip: Assign Ownership of Jobs to the SysAdmin Account

As with most SQL Server best practices, this one comes with a few caveats—in that it might not be the best option in all environments or scenarios. But, for most servers deployed in most SMBs—especially where a few users might have the ability to create jobs or where there might be semi-regular turnover among the folks who are forced to become reluctant DBAs—this simple best practice can save a lot of headaches.

Related: DBAs and the SQL Server Agent

Otherwise, this best practice is exactly as it sounds: For all of your SQL Server Agent jobs, just make sure that as they’re created or modified, that you specify that 'sa' (or the built-in SysAdmin) account should be the Job owner. To check or set this, just right click on a job, select the Properties context-menu option, and then verify or set sa as the owner as needed: 

Why? What’s the Point?

The reason for this is pretty trivial or simple—until you've been bitten by it. Say, for example, that you have a SQL Server with a reluctant DBA that sets up backups at some given point. By default, SQL Server will use their login as the Job owner. So, say, something like SOMECOMPANYMike ends up being the owner. Then, let’s say that 'Mike' decides to move on—to another job or company. Two weeks after his final interview, his domain credentials are finally retired/removed, and this job now begins to fail. As such, a trivial way to make sure you don't get bitten by situations where user permissions might expire, change, or stop working, a simple-to-implement best practice is simply to always make sure that you specify that sa is the owner for each and every job.

NOTE: This approach works even IF or WHEN you're not enabling SQL Server Authentication—as the sa account is always configured. And, even if it's disabled because you don’t have SQL Server Auth enabled/configured, this ‘'rick'" still totally works and jobs will still 100% execute when they’'e owned by sa. Ultimately, sa, in this sense, just basically means that the 'system' owns the jobs—instead of an individual user.

NOTE: If you're using SQL Server Maintenance plans for anything, just make sure that if/when you make any changes to your maintenance plans that you go back in and double-check that the jobs assigned to each maintenance plan end up keeping sa as the job owner. (Most of the time changes will cause the job owner to revert back to the login of the person making the changes. This is also something to take note of with third party backup/maintenance tools as well.)

NOTE: The technique outlined in this post is all about assigning ownership of jobs to the SA account. It is not, however, suggesting that you over-privilege jobs or operations that need to be restricted. Stated differently, in situations where security is a concern, and where least privilege makes sense (such as with jobs other than backups, consistency checks, index defrags, etc.) then you'll want to look at AND take advantage of SQL Server proxies and other security features and capabilities to make sure that operations are handled securely. However, even in many of these cases, you can STILL make SA the owner of these jobs (instead of letting them be owned by the operators that created them).