THE SQL Server Blog Spot on the Web

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

Tibor Karaszi

Using sa as owner for jobs and databases

This blog is not about avoiding logging in using the sa login. Hopefully we all know about this, and work towards avoidning this practice.

Instead I want to talk about using sa, but not to login (authenticate), but as owner for jobs and databases. I want keep these thing de-individualized - so we avoid things like person A leaving the company and we don't dare to remove that login/Windows account. We can of course create some SQL login or Windows login especially for this purpose and use that. But sa is already there. Another advantage is that sa always has the same sid number (makes moving databases across instances a bit easier).

The way Agent work is that if the owner is member of sysadmin server role, then it won't attempt any imersonation for the job steps. I.e., Agent won't use SETUSER (2000 and earlier) or EXECUTE AS USER = (2005 or later). This means that Agent will never actually authenticate using sa (Agent will always authenticate using a Windoes authentication - and then verify that it is sysadmin). I.e., we can change password for sa, disable sa, or even run in Windows Only mode.

And, just to be obvious: If the job should be owned by some individual, in order for operating in a proper security context, then we should use that individual as owner and not sa!

How do you handle job and database ownership? Do you have situations where the owner does matter, details?

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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:

@ALZDBA

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 www.sommarskog.se.

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

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement