February 4, 2010 at 2:46 pm
Hello all,
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
ON
(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_CreateSnapshot_NoExecAs
AS
BEGIN
CREATE DATABASE MY_SNAPSHOT
ON
(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
END
The above procedure works perfectly when logged in as [ProxyTest].
Next, I tried putting this into a stored procedure using EXECUTE AS, as follows:
CREATE PROCEDURE dbo.usp_CreateSnapshot_ExecAs
WITH EXECUTE AS 'ProxyTest'
AS
BEGIN
CREATE DATABASE MY_SNAPSHOT
ON
(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
END
GO
GRANT IMPERSONATE ON LOGIN::ProxyTest TO PUBLIC
GO
Unfortunately, when I try to execute the above procedure, it fails even when logged in as [ProxyTest] or [sa].
The resulting error is:
Msg 262, Level 14, State 1, Procedure usp_CreateSnapshot_ExecAs, Line 7
CREATE DATABASE permission denied in database 'master'.
Does anybody understand what the problem is here?
Thanks!!
February 4, 2010 at 6:54 pm
Has anybody seen this before?
Does anyone have any other suggestions for locking down the stored procedure without using EXECUTE AS?
February 5, 2010 at 12:52 pm
Hello all,
I found the source of my problem, which also describes why this setup worked for us in DEV, but not TST.
Long story short, the [master] database was set to TRUSTWORTHY in DEV, but not in TST. So, no matter what rights the proxy user was given, they were being denied through the impersonation context.
According to this article, MS recommends that [master] never be set to TRUSTWORTHY, so we are looking into certificates to provide the necessary functionality.
February 5, 2010 at 3:59 pm
How I actually solved this for now is:
CREATE PROCEDURE dbo.usp_CreateSnapshot_ExecAs
AS
BEGIN
EXECUTE AS LOGIN = 'ProxyTest';
CREATE DATABASE MY_SNAPSHOT
ON
(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
REVERT;
END
Since we are now referencing the Login (whose dbcreator context exists outside of [master]) instead of the User, this works without [master] needing TRUSTWORTHY.
This solution was put in place in the interest of time.
I am still going to investigate a signed stored procedure when I have time though.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply