2

MSDN suggests that CREATE DATABASE permissions are all that is necessary to create a snapshot.

Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.

This seems not to be the case. When trying to create a snapshot via a user with this suggested minimal permission requirement, I get the following error:

Msg 1817, Level 14, State 1, Line 4
Only the owner of database "example" or the system administrator can create a database snapshot on it.

As I'd like to set up an automated process to create snapshots on a schedule, I obviously would like to avoid giving it db_owner or sysadmin rights.

Is it possible to set up as suggested by MSDN or are their docs wrong?

Many thanks for any suggestions.

/*as sysadmin*/

USE [master];
CREATE DATABASE example;
CREATE LOGIN testuser WITH PASSWORD='xxx'; 
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [example];
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [master];
GRANT CREATE DATABASE to testuser;
go


/*as testuser*/

USE [master];
create database example_snapshot_01
on (
name = example,
filename = 'C:\...\example_snapshot_01.ss'
)
as snapshot of example;
go


/*
Error:
    Msg 1817, Level 14, State 1, Line 4
    Only the owner of database "example" or the system administrator can create a database snapshot on it.
*/
  • Also. There's no difference in behaviour if CREATE ANY DATABASE permission is granted instead. – chrisb Jun 15 '16 at 11:08
2

In order to be able to create a database snapshot, you will need CREATE DATABASE permissions as well as db_owner on the database you want to snapshot. I have tested this and it works perfectly fine.

The other solution is to grant dbcreator server role to the login that needs this privilege. This works too.

It seems that with 'Any user who can create a database can create a database snapshot' this BOL means login having dbcreator server role.

  • Such a shame that dbcreator actually conveys create, alter, drop, and restore. – chrisb Jun 15 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.