Microsoft SQL Server forum - Backup User

Author

Backup User - Server Role?

rcamarda

2005-08-29, 1:24 pm

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?
Any Suggestions?
TIA
Rob
Backgroup: We currently have about 10 SQL servers, and adding more in
the future. I am using SQLBackup from Idera along with HP Surestore
Tape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) with
ArcServe from Computer Associates. I want to have this automated to
backup to file then tape, regardless of what databases get created.

Erland Sommarskog

2005-08-29, 8:24 pm

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/techin.../2000/books.asp

Sponsored Links