Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SreenivasBora
Posting Yak Master
164 Posts |
 Posted - 2005-08-18 : 14:55:36
|
| Hi,Any one can answer below question....I have DB_Owner permission to xx_DB on Test server. I dont have permission to create database on Test server, but I have backup permissions of existing databases.While I am restoring database into xx_db it is giving an error like "USER DOES NOT HAVE PERMISSION TO RESTORE DATABASE xx_db. RESTORE DATABASE is terminating abnormally"What permission my user require? I dont want to give sysadmin permissions.RegardsBSRWith RegardsBSR |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-18 : 14:58:57
|
From SQL Server Books Online RESTORE DATABASE:quote:
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. 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.In addition, the user may specify passwords for a media set, a backup set, or both. When a password is defined on a media set, it is not enough that a user is a member of appropriate fixed server and database roles to perform a backup. The user also must supply the media password to perform these operations. Similarly, RESTORE is not allowed unless the correct media password and backup set password are specified in the restore command.Defining passwords for backup sets and media sets is an optional feature in the BACKUP statement. The passwords will prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2000 tools, but passwords do not prevent overwrite of media with the FORMAT option.Thus, although the use of passwords can help protect the contents of media from unauthorized access using SQL Server tools, passwords do not protect contents from being destroyed. Passwords do not fully prevent unauthorized access to the contents of the media because the data in the backup sets is not encrypted and could theoretically be examined by programs specifically created for this purpose. For situations where security is crucial, it is important to prevent access to the media by unauthorized individuals.It is an error to specify a password if none is defined.
Tara |
 |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2005-08-18 : 15:13:46
|
| Hi Tara,I have seen this information. Any how thanks for sending it.Let me try with your info, if any problem I will come again....ThanksSreeni.With RegardsBSR |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-08-19 : 11:43:01
|
| I believe dbcreator would be required to restore a database, but I have never tried it. DOH! It is in the first paragraph of Tara's post. Guess I shouldn't have played hooky so often in that, er,...reedin'...class. |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-03-19 : 15:48:41
|
| I'm having the same problem and I'm unable to resolve it. I have a C# application that connects to a SQL database and runs a restore query. It works when I run it on my computer, but when I publish it to another computer on my network I get the error stated by the author. I don't understand it because the C# code is the same meaning it's using the same SQL connection string ie. the same login. That login has sysadmin and dbcreator server roles and is also db_owner of the database that I'm trying to restore. |
|
|
|
|
|
|
|
|
Restore database Failed ....