Posted by Erland Sommarskog on 08/30/05 00:28

rcamarda (rcamarda@cablespeed.com) writes:
> I wish to create a user that can backup any or all databases in our SQL
> Server 2000 Instance. I thought there would be a server role for this
> function, however I can only find that after I grant access of a
> database to the user, then I can choose ds_backupoperator.
> I want to create a user that will have the ability to backup all the
> databases. I dont wish to have to come back to the server after a new
> table is created and add the backup user to that table.
> I want SA w/o the full privilage...am I crazy?

I guess the reason you need to be sysadmin to backup a database, is that
BACKUP DATABASE gives you access to the data in the table. Once you have
a backup, and can restore anywhere you like - and with any privilege.

I believe there is a way to add a user to db_backupoperator to all
future database: add the user to this role in model. I have not tried
this, though.





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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]