• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2221
  • Last Modified:

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

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?


RESTORE DATABASE [my_db] 
FROM  DISK = N'E:\SQLBCKP\FULL-Mar15-Updtd-Scrubbed.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10


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

0
Bruce
Asked:
Bruce
5 Solutions
 
mrjoltcolaCommented:
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)
0
 
SQLSergentMikeCommented:
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.

0
 
Alpesh PatelAssistant ConsultantCommented:
You need db_creator permission to do this.
0
 
Alpesh PatelAssistant ConsultantCommented:
db_owner means you have read and write permissions, plus backup rights
0
 
gladfellowCommented:
"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:
http://msdn.microsoft.com/en-us/library/ms186858.aspx

Hope it helps.
- Venkat
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now