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