1

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?

Improve this question

2 Answers 2

Reset to default
3

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
1

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

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.