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:

If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database

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

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