I am working with a vendor who is developing a SQL Server database outside of our domain. Occasionally, they need to deploy a new copy of that database into our domain by backing up their database, and restoring over the top of the of the database within our domain.
The vendor uses a domain login on our side to access this database. This domain login is also the owner of this local copy. This vendor is trusted enough that we have granted the login the "dbcreator" server role. A combination of database ownership and the dbcreator role allows this domain login to restore a copy of the database over the top of the existing database. The restore completes, but as soon as it's done, the domain login can no longer access the database because the database ownership is lost. (I believe because it had a different owner on the vendor's system). After the restore is complete, I have to re-create the user within the database that matches up to the domain login, and make that user the owner again. The domain login can then access the database again.
Is there any way I can configure this so the vendor is able to perform the restores when they need to without losing the existing ownership? This way I would not have to be involved every time they deploy a new version of the database. I cannot give them sysadmin because there are other databases on the server.
Best Answer
Correct. While it's possible (I believe) to have the same SID (i.e. Security ID) between their system and yours (if so, it would require you creating their domain account in your domain with the SID that they provide that is the SID of the owner account on their side), most likely it's a different SID, and that is retained in the database itself.
Now this doesn't sound right. Usually, if you add an SID to a database as a User, and then later try to make a Login (having that same SID) the owner of the database, you will get an error stating that the account / SID is already present in the database:
Msg 15110, Level 16, State 1, Line XXXXX
The proposed new database owner is already a user or aliased in the database.
It's possible that something is misstated here, or that I am misunderstanding something, but as we will see in a moment, it doesn't really matter.
It's nice that you can trust them, but you don't need to do anything special for them. You can allow them to restore their database, and automatically set the owner to their domain account, and anything else that needs to happen in this process, all while removing them from the
dbcreator
fixed Server Role. All their login needs is the ability to connect to the instance (and they already have that), and permission to execute a single stored procedure. What stored procedure? The one I will now describe :-)Using the wonderful awesomeness of Module Signing, you can set up a stored procedure that will a) execute the
RESTORE DATABASE
command, b) set their login as the owner of that database, and c) perform any other administrative steps needed for this operation, no matter what level of permissions are needed. You will never give the vendor's login any permissions except for the ability to execute this single stored procedure. The necessary permission(s) will instead be given to the stored procedure.Assuming that you have a
[Utility]
(or whatever name) database for general administrative functionality, do the following:[Utility]
database:CREATE USER
RestoreVendorDB
stored procedure that does the following:RESTORE DATABASE [VendorDB] ...
ALTER AUTHORIZATION ON DATABASE::[VendorDB] TO [DOMAIN\VendorLogin];
GRANT EXECUTE ON [dbo].[RestoreVendorDB] TO [DOMAIN\VendorLogin];
CREATE CERTIFICATE...
ADD SIGNATURE...
[master]
database using Dynamic SQL to executeCREATE CERTIFICATE
with result ofCERTENCODED(CERT_ID(...))
[master]
DB, do the following:CREATE LOGIN [Permission$CreateVendorDB] FROM CERTIFICATE [...];
dbcreator
role usingALTER SERVER ROLE [dbcreator] ADD MEMBER [Permission$CreateVendorDB];
GRANT
the certificate-based login the appropriate permission(s).dbcreator
role (they no longer need that permission) usingALTER SERVER ROLE [dbcreator] DROP MEMBER [DOMAIN\VendorLogin];
If you ever alter the code in the
RestoreVendorDB
stored procedure, just re-sign the stored procedure (after theALTER
) usingADD SIGNATURE...
.Using this approach, you not only get exactly what you were wanting, but you also end up with a more secure system ? .
For more explanation and a working example (of this concept), please see:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level