Sample Header Ad - 728x90

How to maintain database ownership restoring across domains?

4 votes
1 answer
1037 views
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.
Asked by SomeGuy (2053 rep)
Nov 24, 2014, 07:38 PM
Last activity: Oct 25, 2019, 01:30 PM