4

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

Skyhawk
  • 14,200
  • 4
  • 53
  • 95
Hubert Kario
  • 6,361
  • 6
  • 36
  • 65

2 Answers2

2

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

Skyhawk
  • 14,200
  • 4
  • 53
  • 95
Mathieu Chateau
  • 3,185
  • 16
  • 10
  • A variation on this might be to let the developers manually do the restore (which will let them pick a database name and paths for the MDF and LDF files. They might need to do this if restoring the same production backup to two different databases, maybe one for DEV and one for QA). Then, as Mathieu suggests, have a SQL Agent job that just looks for databases where the developers are not db_owners and then adds them to the db_owner rolet. Of course, you would want to skip some databases (master, msdb, tempdb and model, at least) or the devs might do something accidentally. – Darin Strait Apr 16 '12 at 21:04
  • @darinstrait: thing is, only certain developers should have access to specific databases. That is dev A to db X but not db Y, dev B to db Y, but not db X and so on. Otherwise I would just give them admin access to the db server... – Hubert Kario Apr 18 '12 at 17:50
  • That seems like the best solution. If you'd be so kind to provide reference script I'll gladly award the bounty to you. – Hubert Kario Apr 19 '12 at 15:02
  • @hubert: If devs can't be in certain databases, I'm not sure that letting them clobber a database through a restore is good either, plus IIRC if they can restore something they can back up anything. That means they could backup a 'forbidden' database, restore that to somewhere (or copy it to anywhere and maybe take it home or a competitor) and they are in. So, maybe Mathieu's idea is best. – Darin Strait Apr 19 '12 at 15:23
  • @darinstrait, no, if developers can restore or create databases they can *delete* any database, but can't export databases in which they don't have backupoperator, dbowner or similar role. – Hubert Kario Apr 19 '12 at 17:29
  • @HubertKario here we go: http://www.lotp.fr/2012/04/restaurer-une-base-sql-server-via-powershell/ – Mathieu Chateau Apr 19 '12 at 20:38
  • @MathieuChateau: Thank you, but if I understand the script correctly, it doesn't add the owner of the file to the dbowner database role... – Hubert Kario Apr 20 '12 at 11:42
0

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.

Mark
  • 2,248
  • 12
  • 15