Link to home
Start Free TrialLog in
Avatar of TheAvenger
TheAvengerFlag for Switzerland

asked on

Create Snapshot Fails

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
Avatar of Lukasz Zielinski
Lukasz Zielinski
Flag of Poland image

try adding DB owner name before database name for instance dbo.DATABASE1

ziolko.
Avatar of TheAvenger

ASKER

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.
did you try running this SQL when logged as owner of MyDB?

ziolko.
Yes, it runs. However the owner is 'sa', a very powerful user.
in this case I'm afraid you have to change db owner either by re-creating db as other user or sp_changedbowner

ziolko.
ASKER CERTIFIED SOLUTION
Avatar of TheAvenger
TheAvenger
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok.

ziolko.