I am working on setting up a proxy account to use for creating a database snapshot from within a stored procedure in [master].
The login [ProxyTest] has been granted the server roles of [dbcreator] and [public]. The login also has a corresponding user of the same name in [master] which has been granted [db_owner]. The code I am using to create the snapshot is:
CREATE DATABASE MY_SNAPSHOT
(Name = 'MyDBData', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_1.SNAP'),
(Name = 'MyDBData2', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_1_2.SNAP'),
(Name = 'MyDBData3', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_2_3.SNAP'),
(Name = 'MyDBData4', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_3_4.SNAP'),
(Name = 'MyDBData5', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_4_5.SNAP'),
(Name = 'MyDBData6', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_5_6.SNAP'),
(Name = 'MyDBData7', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_6_7.SNAP'),
(Name = 'MyDBData8', Filename = 'E:\MSSQL.3\DATA\MY_SNAPSHOT_MyDB_7_8.SNAP')
AS SNAPSHOT OF MyDB
The above code works perfectly when logged in as [ProxyTest].
I have put this into the following stored procedure:
CREATE PROCEDURE dbo.usp_
View Complete Post