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