Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to give a user server-level permissions, once and for all time, to back-up any existing or future database on the server instance, without making the user sysadmin. How can I do that?

I've tried giving users: dbcreator, serveradmin, setupadmin, but the user still can't backup a database unless she is a db_owner.

My team does a lot of testing setup and synchronization of environments. Team members need to be able to create new databases at will and backup and restore at will. Currently, we have to keep giving people db_owner rights on newly created databases. Any way to avoid this?

share|improve this question
add comment

2 Answers

I don't know of any server level options per se, but a few options might works.

  1. Modify the model DB so that it has the backup operators role assigned to a group with your user in it.

  2. Run a t-sql script nightly that loops through each db and assigns the role.

Also, just to re-affirm, see here... http://dba.stackexchange.com/questions/28472/is-there-a-backup-operator-role-in-microsoft-sql-server-2008

share|improve this answer
add comment

IIRC, you can grant the "BACKUP DATABASE" and "BACKUP LOG" permissions to a user. SQL Server 2008 will not allow you to create a server-level role, so you might be stuck granting these permissions on a user-by-user basis. See this page and look under "Remarks".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.