?
Solved

Create Snapshot Fails

Posted on 2007-08-02
9
Medium Priority
?
307 Views
Last Modified: 2008-03-10
Hello guys

I am trying to create a snapshot of a database in my application using the CREATE DATABASE statement. Everything works fine when I run the application as an administrator of the server. However it fails when I use a more restricted user.

I found the following text in Books Online under the topic How to: Create a Database Snapshot (Transact-SQL):

"Any user who can create a database can create a database snapshot. The only way to create a snapshot is to use Transact-SQL."

My restricted user has the rights to create any database on the server, however when calling the CREATE DATABASE statement I get the following error:

"Only the owner of database "MyDB" or the system administrator can create a database snapshot on it."

So, my user can create databases on the server but is not allowed to create a database snapshot. Could it be that this is a setting of the database of some kind? Or is just the documentation wrong? If it is wrong, how could I give the user right to create a snapshot without making him omnipotent (admin) on the server?

TheAvenger
0
Comment
Question by:TheAvenger
  • 4
  • 3
7 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 19615202
try adding DB owner name before database name for instance dbo.DATABASE1

ziolko.
0
 
LVL 20

Author Comment

by:TheAvenger
ID: 19615262
My syntax is:

CREATE DATABASE MyDB_Snapshot
ON ( NAME = N'MyDB_Data',
FILENAME = N'd:\data\MyDB_Snaphost_Data.ss' )
AS SNAPSHOT OF MyDB

The dbo. is set in front of database objects like tables. It does not work in front of a database name.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19615281
did you try running this SQL when logged as owner of MyDB?

ziolko.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 20

Author Comment

by:TheAvenger
ID: 19615290
Yes, it runs. However the owner is 'sa', a very powerful user.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19615320
in this case I'm afraid you have to change db owner either by re-creating db as other user or sp_changedbowner

ziolko.
0
 
LVL 20

Accepted Solution

by:
TheAvenger earned 0 total points
ID: 19615332
I have 100 users who should do this. I cannot change the db owner.

I found out that it works if the user has the right "Alter any database" but it does not work with "Create any database".

I will put a request to close this question with point refund.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19615336
ok.

ziolko.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

839 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