Minimal permission for restoring *some* databases on SQL Server 2012

Minimal permission for restoring *some* databases on SQL Server 2012

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?

Thanks & Regards
Camillelola

                  New to ADSelfService Plus?