If I have "db_owner" rights to my MSSQL database, why can't I restore (with replace)?

Posted on 2011-04-27
Last Modified: 2012-08-14
I am a contractor for a large financial services company so I am not in control of my virtual SQL server instance.  I have been granted owner rights on the database I've been using.  

Until just recently I have been doing my own backups and restores during my testing of my app.  

Now, even though I have db_owner, the DB server is not allowing me to execute the script I've been running for weeks.

See code... and error...

What rights do I need to have restore to a database?  

And, are there any SQL server "Policies" that could revoke that right?

FROM  DISK = N'E:\SQLBCKP\FULL-Mar15-Updtd-Scrubbed.bak' 

Msg 3110, Level 14, State 1, Line 2
User does not have permission to RESTORE database 'my_db'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Open in new window

Question by:Bruce
    LVL 40

    Assisted Solution

    If you are trying to re-create the database (ie. doesn't exist at the time of the restore), check that you have CREATE DATABASE priv (or db_creator I believe)
    LVL 2

    Assisted Solution

    RESTORE permissions default to members of the sysadmin and dbcreator fixed
    server roles and the owner (dbo) of the database ... members of the db_owner
    fixed database role do not have RESTORE permissions.

    LVL 21

    Assisted Solution

    by:Alpesh Patel
    You need db_creator permission to do this.
    LVL 21

    Assisted Solution

    by:Alpesh Patel
    db_owner means you have read and write permissions, plus backup rights
    LVL 1

    Accepted Solution

    "RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions."

    The above explanation has been quoted from the MSDN article available at:

    Hope it helps.
    - Venkat

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now