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 sysadmin
.
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 sp_addrolemember
requires 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).
You may workaround the issue by:
So developers just have to place a backup file on the shared folder to get it restored, without even dbcreator privilege.
If you are interested in, I can provide a powershell script example to restore database (and change path as needed for example).
If your sources are fairly standard, you could make a stored-proc that only that role can run that takes the restore file, new db name, and user-id as arguments. Have one SP restore the DB and then do the permission change.
With that set up, you can probably stop giving users the dbcreator role and just run the SP as sysadmin. Just grant the privileged users rights to the SP (or a role/schema that includes that SP) and have it sanity check & bracket the arguments.
Since you are using powershell already, you have the logic for the restores figured out. Just move most of it to SQL and have the powershell script collect the arguments and then call the SP.