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.
*/
CREATE ANY DATABASE
permission is granted instead. – chrisb Jun 15 '16 at 11:08