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?