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).
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.
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.
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.
This vendor is trusted enough that we have granted the login the "dbcreator" server role.
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:
- In the
[Utility] database:
- Add the DOMAIN\VendorLogin to the DB using
CREATE USER
- Create a
RestoreVendorDB stored procedure that does the following:
- Executes
RESTORE DATABASE [VendorDB] ...
- Executes
ALTER AUTHORIZATION ON DATABASE::[VendorDB] TO [DOMAIN\VendorLogin];
- Allow the vendor to execute the stored proc:
GRANT EXECUTE ON [dbo].[RestoreVendorDB] TO [DOMAIN\VendorLogin];
- Create a certificate using
CREATE CERTIFICATE...
- Sign the stored procedure with that certificate using
ADD SIGNATURE...
- Copy certificate (only the public key) to
[master] database using Dynamic SQL to execute CREATE CERTIFICATE with result of CERTENCODED(CERT_ID(...))
- In the
[master] DB, do the following:
- Create a login from the certificate using
CREATE LOGIN [Permission$CreateVendorDB] FROM CERTIFICATE [...];
- Add the certificate-based login to the
dbcreator role using ALTER SERVER ROLE [dbcreator] ADD MEMBER [Permission$CreateVendorDB];
- If you need an additional role and/or permission in order for the stored procedure to change the database's owner, then repeat the previous step with the appropriate role and/or
GRANT the certificate-based login the appropriate permission(s).
- Remove the vendor's login from the
dbcreator role (they no longer need that permission) using ALTER 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 the ALTER) using ADD 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
Best Answer
Yes you would need a Login for that domain user to exist first. If all they need is the ability to manage databases (create, alter, drop, restore them) then the server role dbcreator should be sufficient.
If you anticipate multiple domain users needing access to this, then you should instead create a Login for a correlating Active Directory Group, so you don't have to continually manage setting up multiple Logins. You will then be able to map that AD Group to the server role dbcreator. (You can even use the built in "Domain Users" group if you wanted everyone on the domain to have access to creating databases.)
As far as how to accomplish this in C#, there's a multitude of ways, and that's perhaps a better question for StackOverflow because it's dependent on how you're accessing and querying your server instance. For example, with ADO.NET you could simply write a SQL query to check if a Login exists for the current user, if not create it, and assign the role. Or you can even create a procedure that takes in the current user of the application, and handles that logic for you (which will be more consumable depending on what database access framework you're using). But you'll likely have the easiest time managing this by creating an appropriate AD Group and mapping it to a Login with the dbowner role once.