I am trying to create a set of permissions where an SQL login can create backups of all databases, but only restore the backups on the TEST databases. Another login should be used for restoring to LIVE (which is on the same SQL server).

Here it states:


Based on this I have tried making the login the owner of the TEST databases with db_owner db permission, but without the sysadmin server role. On the LIVE databases the login has: db_accessadmin, db_backupoperator, db_datareader, db_denydatawriter, db_securityadmin (and no ownership).

But Restore still does not work at all. If I try to perform a restore I cannot view the backupsets in a .bak file. I think the dialog is doing a RESTORE HEADERONLY in the background; if I run RESTORE HEADERONLY FROM DISK = 'D:\Path\backup.bak' I get CREATE DATABASE permission denied in database 'master'. I can view the backupsets fine with the sysadmin role.

Is this possible without sysadmin?


For all those DBA's screaming at my question, I thought I'd put you out of your misery:

I decided that the proper way to solve this is to create a second named instance of SQL server, so that there is extra separation between Live and Test DBs. Then you can set up whatever permissions you like for each instance.

It seems you need to have sysadmin OR dbcreator server role AND db_owner database role for RESTORE

Your Answer

