Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
Ah - an entry in BOL I missed; cheers. –  Chris J Apr 12 '11 at 16:23
    
Glad it helped :-) –  Sankar Reddy Apr 12 '11 at 16:26
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.