Sorry for the duplicate post, but I thought a subject change might
elicit some responses.

We are getting ready to disable the builtin\administrators group per a
SOX requirement.  We use a 3rd party tool to backup our SQL databases
(TDP for SQL), that currently uses "local service".  Is this
sufficient to back up our databases after we disable the builtin
accounts, or will my backups break?  If not, what are my options?

Here are the ones I see:

1:  Set up windows account, and add it to the sysadmin role at the
server level.
2:  Set up windows account, and add it to the backup operator role in
each database on the machine.
     Does the backup operator also have create/move/restore privilages
or is that altogether different?

Does anyone have an idea why they would not assign a backup operator
at the server level?

TIA!

David Hay

David Hay (david.hay@gmail.com) writes:
> We are getting ready to disable the builtin\administrators group per a
> SOX requirement.  We use a 3rd party tool to backup our SQL databases
> (TDP for SQL), that currently uses "local service".  Is this
> sufficient to back up our databases after we disable the builtin
> accounts, or will my backups break?  

I would think so, but testing is always a good idea.

> 1:  Set up windows account, and add it to the sysadmin role at the
> server level.
> 2:  Set up windows account, and add it to the backup operator role in
> each database on the machine.
>      Does the backup operator also have create/move/restore privilages
> or is that altogether different?

Books Online says:

   If the database being restored does not exist, the user must have CREATE 
   DATABASE permissions to be able to execute RESTORE. If the database 
   exists, RESTORE permissions default to members of the sysadmin and 
   dbcreator fixed server roles and the owner (dbo) of the database 
 
So being member of the backup operator role is not sufficient, if
you want this service to also be able to restore databases.

This seems to boil down to that you need to give the service sysadmin -
which of course is not very palatable.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

David Hay wrote:
> We are getting ready to disable the builtin\administrators group per a
> SOX requirement.

Note that a database cannot be effectively secured against having its data 
extracted by a sufficiently determined administrator who can log on to the 
machine itself, even if SQL Server doesn't cooperate. You need to secure 
logon access to the server as well (local and remote). I'm assuming the 
requirements cover this, as it's more basic than SQL Server security, but it 
still bears mentioning.

-- 
J.

On Dec 7, 6:43=A0pm, Jeroen Mostert <jmost...@xs4all.nl> wrote:
> David Hay wrote:
> > We are getting ready to disable the builtin\administrators group per a
> > SOX requirement.
>
> Note that a database cannot be effectively secured against having its dat=
a
> extracted by a sufficiently determined administrator who can log on to th=
e
> machine itself, even if SQL Server doesn't cooperate. You need to secure
> logon access to the server as well (local and remote). I'm assuming the
> requirements cover this, as it's more basic than SQL Server security, but=
 it
> still bears mentioning.
>
> --
> J.

Jeroen,

I understand that, I think the auditors do to. We just need a well
defined separation of duties and access control.  The windows admins
do windows, the DBAs do SQL.

Thanks!

Erland,

Thanks, testing it is.  I'll let everyone know how it turns out.  I'm
sure I'm not the only one in this predicament.

David