If I'm reading Books Online correctly, a user can restore an existing database if they're the DBO of that database; the actual words are:
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
Unfortuantly, this doesn't seem to be exactly correct or there's a subtlety I'm missing. For a specific database, I'm trying to get this to work but when the user does a RESTORE FILELISTONLY to get the files in the backup file, we get the error:
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
It's a slightly confusing error message as at this point all we're doing is trying to read the backup file, not create, nor even restore an existing, database; so I can only assume SQL Server attempts to do a permissions check before allowing any RESTORE command to execute.
Now BOL doesn't mention any extra permissions needed, so am wondering which is correct, or am I missing a subtlety in the text. Whilst I don't doubt the error messages accuracy, what do I really need to do to get a user who is db_owner of a database to be able to restore that database, without elevating their permissions?
There has been a change in this area in and above SQL Server 2008 compared to SQL Server 2005 and below. Some of it is documented in the below.
http://msdn.microsoft.com/en-us/library/ms173778.aspx
http://msdn.microsoft.com/en-us/library/ms178569.aspx
My understanding is that you need membership of the db_creator role - basically, SQL Server can't tell whether you are db_owner of a database that is yet to be restored! I see what you are saying though...