THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Using sa as owner for jobs and databases

This blog has moved! You can find this content at the following new location:

Published Wednesday, December 30, 2009 9:19 AM by TiborKaraszi



ALZDBA said:

My guidelines are:

- prefer a sqluser (avoid windows logins because if a domain controller gets rebooted, win auth may fail the handshake process until sqlagent has been restarted (sql2005 sp3) )

- if someone needs more auth (windows), grant the use of a proxy account that has been granted the needed stuff. We only create proxy accounts for specific requests.

- in many cases users develop jobs that should run "out" of their projects db context. These jobs will be given sa for job owner.

- use sa for all systems stuff (backup, monitor,...)

December 30, 2009 5:10 AM

Tim Ford said:

Hi Tibor, are you saying that this will eliminate context switches when the jobs are run?  I tend to have a dedicated SQL login that owns all jobs and databases unless there is a requirement for a specific login.  

December 30, 2009 7:30 AM

Dave Wentzel said:

Here's one where the owner *appears* to matter.  I've found that I need to give permissions to sql logins/users to execute things such as sp_configure and xp_cmdshell from within their user db.  Giving permissions directly is a no-no.  The easiest solution (maybe not the best) is to ensure the dbowner is sa and the database is set to trustworthy.  Then I can create wrapper procedures WITH EXECUTE AS 'dbo'.  The users that need access are granted EXEC perms on the wrapper.  I've found this only works with the dbowner set to sa.  With these wrapper procedures I can get far more granular permissions than what MS supplies out-of-the-box.  

December 30, 2009 10:05 AM

Chad Miller said:

I set all jobs and databases owned by sa. To me this makes sense to avoid using an individual DBA Windows logins (which are orphaned when DBA leaves). Also on my service accounts I will create a brand new service account at least once year or more if a DBA leaves. So, setting job owners or databases to a Windows service account will generate unneeded work. For database owners orphaned owners cause SQL-DMO script errors in MOM 2005 -- another reason to use sa for owners.

December 30, 2009 10:06 AM

Kevin Devine said:

We use sa for all our jobs and databases and we only recently were able to change old linked servers from using sa context when we upgraded from 2000 to 2008.

I recently wrote a Web Service that creates a job wrapper around an SSIS package (so it will run server-side) and I wasn't going to allow the Web Service to run with sysadmin rights so I learned a lot about Credentials and Proxy Accounts, so I might start to change my mind about all the sa permissions and start delegating rights...  We did have some memory leaks associated with proxy accounts on 2005, so that might not be a solution...

December 30, 2009 9:11 PM

RichB said:


Have you ever run a network trace on a SQL User?  eg Wireshark?

They pass passwords around in plain text...  insecure?

January 4, 2010 8:35 AM

TiborKaraszi said:

Thanks all för sharing and commenting :-). Here are a few replies, comments:

Tim, I'm not sure what you mean by context switches (those two words can mean many different things). Can you elaborate? If the job owner is sysadmin (doesn't matter exactly who - being sysadmin is what counts), then no impersonation is attempted (EXECUTE AS for TSQL steps or using proxy accounts for other job subsystems).

Dave, I hear you.One option could be using certificates and module signing. I haven't had the need to play with that a lot, but Erland has a great article on that topic at

Kevin,Thanks for sharing about memory leaks using proxy account. One think to keep an eye on. I mostly del with environments where jobs are at the "system" level, and having somebody who is sysadmin as owner is fine (i.e., I don't deal much with proxy accounts).

Rich, As of 2005, passwords for SQL logins are always encrypted. Are you saying that you have cases where SQL logins passwords are not encrypted? However, this discussion is about who owns a job or a database. No actisal atuctentication is performed. So, the discussion about what is best SQL account or Win accounts doesn't eally apply here... :-)

January 5, 2010 2:36 AM
New Comments to this post are disabled
Privacy Statement