Give a user access to restore a database that they are owner on without the server role dbcreator

restorerolesql server

A outside vendor has a SQL user with db_owner rights on two databases, live and training. From time to time they would like to be able to backup live and restore on top of training. The easiest thing would be to grant them the server role dbcreator but that would allow them to drop databases they wouldn't normally have access to. Right now they can make the backup from live but restoring to training they get:

A exception occurred while executing a Transact-SQL statement or batch:
CREATE DATABASE permission denied in database 'master'
RESTORE HEADERONLY is terminating abnormally

Is there a way to give this user access to restore the database without giving them the server role dbcreator or giving them that permission but then limiting the scope to only the two databases they should have access to?

Best Answer

You can do this by guaranteeing that the account vendor is using does not have a corresponding user in any databases other than these two.

CREATE ANY DATABASE will give the vendor privilege to do the required restore.

The following code will demonstrate that the user vendor cannot drop databases.

CREATE LOGIN vendor
  WITH PASSWORD = N'Change_Password',
  CHECK_POLICY = OFF;
GO

GRANT CREATE ANY DATABASE TO vendor
GO

USE master;
GO
IF  EXISTS (
    SELECT name 
        FROM sys.databases 
        WHERE name = N'live'
)
DROP DATABASE live;
GO
CREATE DATABASE live;
GO

ALTER AUTHORIZATION ON DATABASE::live TO vendor;
GO

USE master;
GO

IF  EXISTS (
    SELECT name 
        FROM sys.databases 
        WHERE name = N'training'
)
DROP DATABASE training;
GO

CREATE DATABASE training;
GO

ALTER AUTHORIZATION ON DATABASE::training TO vendor;
GO

USE master;
GO
IF  EXISTS (
    SELECT name 
        FROM sys.databases 
        WHERE name = N'cannotdropme'
)
DROP DATABASE cannotdropme;
GO
CREATE DATABASE cannotdropme;
GO

EXECUTE AS LOGIN = 'vendor';  
SELECT SUSER_NAME(), USER_NAME();  
 
BACKUP DATABASE live
    TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\live.bak' 
WITH 
    NOFORMAT, 
    COMPRESSION,
    NOINIT,  
    NAME = N'live-Full Database Backup', 
    SKIP, 
    STATS = 10;
GO

USE [master]

RESTORE DATABASE [training] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\live.bak'
WITH  FILE = 1,  
MOVE N'Live' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\training.mdf',  
MOVE N'Live_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\training_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5
GO

--Cannot drop this database as user verndor is not a user in this database
DROP DATABASE cannotdropme;
GO

REVERT;  
SELECT SUSER_NAME(), USER_NAME();  

DROP DATABASE live;
DROP DATABASE training; 
DROP DATABASE cannotdropme;
DROP LOGIN vendor;  
GO
Related Question