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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply