SQL Server 2008 - Put database user permissions back in after restore without sa privileges

Question

What would be the best way to allow users to manage their own database restores from backup files? The reason I ask is because I have a user who is the db_owner for a db. He loses access as soon as he restores the database from a backup file from another instance(of course because he does not have access on that instance), and then someone with sa permissions has to restore his permissions. Is there anyway he can restore the backups, and then put back the db owner permission on the database that he already had?

Answer 1

Your best bet at this point is to create a custom stored procedure that does the following style pseudo code:

1. Take the database name, backup file name
2. Check to make sure the person requesting is the owner
3. Restore the database over the original
4. Set the owner back to the original in #2

Build in some logic to check and make sure people aren't trying to abuse it, give bad values, etc. Sign the procedure with a certificate and use a special account just for this purpose to lock down possible intruders/malicious people.