I have databases from different projects on one server. I can't give all developers administrative (
sysadmin) access to the databases. They have to have the permissions needed to create and restore databases. The
dbcreator server role works quite nicely for that. In other words, the users that restore the backups have
dbcreator but not
Unfortunately, because most of the restored backups don't come from the same server that they are restored on, users that restore the backups immediately lose access to the database that they just have restored.
How can they restore a database so that the user that restored database from backup is automatically added to
dbowner database role? What changes are needed in SQL Server to make that possible?
Update: I've tried to add trigger on INSERT to
[dbo].[restorehistory], but to add role using the
use [database] to work and this statement is illegal in a trigger. I've also read that triggers on
restorehistory don't fire at all after database import (as it's a system table).