Home Forums SQL Server 2005 Administering Backup Operators -- what are the BEST practices for SQL backups? RE: Backup Operators -- what are the BEST practices for SQL backups?

  • subs99 (12/30/2009)


    Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.

    Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?

    Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)

    I'm not sure where you have worked that DBA's say "just use sa" as I've yet to run into one who says that. The db_backupoperator Database Role is there so you can allow users to run backups of specific databases without needing sa access. This role is rarely, if ever used, because most DBA's schedule backups and don't want users taking backups because it will break the backup chain (unless you use copy only, but how many users know about that?). K. Brian Kelley has a good write up on the Fixed Database Roles over a MSSQLTips where he gives several reasons why this role is usually not used.

    Using sa as a job owner isn't, in my opinion, a security risk. In most cases only users with sysadmin privileges can create jobs so they can already do anything on the server. Yes, starting with SQL Server 2005 there are Agent Roles, but I'd bet those are rarely used.

    I think you misunderstood what Tibor is saying. He's saying using sa can help avoid problems and be fine security wise because:

    ...we can change password for sa, disable sa, or even run in Windows Only mode.

    And jobs will still run. Thus you can just disable the sa account or change the password as often as you want without affecting the status/runnability of the job(s).

    I'm not saying you have to go that way, I'm just saying that I don't really see a security risk involved with this as you still control who can create, view and run jobs and those in the SQLAgent Roles cannot change the owner to sa only sysadmins can.