MS SQL access to restored database without sysadmin role

permissionssql-server-2008sql-server-2008-r2

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).

Best Answer

You may workaround the issue by:

  • Create a folder to host SQL backup
  • Create a script, like a powershell one, to automatically restore to a database when it finds a backup in the folder, apply security, and then move the backup to an archive folder. Then send a mail when it's done for example.
  • Create a scheduled task that runs like every minute.

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).

Related Topic