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?


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... https://dba.stackexchange.com/questions/28472/is-there-a-backup-operator-role-in-microsoft-sql-server-2008

| improve this answer | |

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".

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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