TheAvenger
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
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
ASKER
My syntax is:
CREATE DATABASE MyDB_Snapshot
ON ( NAME = N'MyDB_Data',
FILENAME = N'd:\data\MyDB_Snaphost_Da ta.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.
CREATE DATABASE MyDB_Snapshot
ON ( NAME = N'MyDB_Data',
FILENAME = N'd:\data\MyDB_Snaphost_Da
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.
ziolko.
ASKER
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.
ziolko.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok.
ziolko.
ziolko.
ziolko.