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?