April 26, 2017 at 9:09 am
I've got a developer who needs a fast, easy way to test changes to an application he supports, which has some "interesting" quirks when it comes to the various stored procedures in the database (values get pulled into the SP from multiple tables, sometimes changed and saved, sometimes not.) My thought is to create a stored procedure that he can use to create, revert, or drop a database snapshot of his database (the database name would be hard-coded in the procedure, he could specify the name of the snapshots.)
I've got a procedure that works, when I'm connected to SQL as a sysadmin, but I'm having problems getting it to work with an execute as...
Here's what I've got so far:alter procedure [sp_Snapshot]
@FirstParam char(1) = 'c'
, @SnapName varchar(100)
as
set nocount on;
declare @sql varchar(500)
If @SnapName is null or @SnapName = '' GOTO NoSnapname
If @firstparam = 'C'
set @sql = 'create database [' + @SnapName + ']
ON (
Name = SnapshotTest
, FILENAME = ''m:\userdata\' + @SnapName + '.ss''
)
as snapshot of [SnapshotTest];'
Else if @firstparam = 'R'
set @sql = 'restore database SnapshotTest
from database_snapshot = ''' + @SnapName + ''';'
Else if @firstparam = 'D'
set @sql = 'drop database [' + @SnapName + '];'
Else
Print 'You chose an invalid parameter value'
Print @sql
exec(@sql) as LOGIN = 'Domain\Login With Sufficient Privs'
GOTO ExitProc
NoSnapname:
Print 'No snapshot name was entered'
ExitProc:
go
I *know* the problem has to be the exec(@sql), and I just found I can do a "AS LOGIN = " (Script above now reflects this) which now gives me "Cannot execute as the database principal because the principal "Domain\Login With Sufficient Privs" does not exist, this type of principal cannot be impersonated, or you do not have permission."
I'd really, really, rather not grant impersonation of the login I'm (currently) planning to use, just from a paranoid security standpoint.
So, what am I doing wrong / need to do different to get this to work?
April 26, 2017 at 9:19 am
Never mind, I found a solution, create a dedicated login with create database, and backup on the target database, then use that for the exec as login.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply