Post reply

EXUCUTE AS to Drop & Create Snapshot

  • November 26, 2014 at 3:23 pm

    #313408

    I am writing a stored procedure at the end of which is supposed to drop & recreate a snapshot of the current database. The users running the SP obviously need to drop & recreate the snapshot but should not be able to drop the current database.

    EXECUTE as user = 'SnapShotCreator' -- Member of sysadmin server role

    DROP DATABASE DATABASE1_SNP;

    create database DATABASE1_SNP

    ON (NAME = 'DATABASE1', FILENAME = 'I:\SQLData\User\DATABASE1_SNP.snp')

    As SNAPSHOT of DATABASE1

    REVERT

    -- I have previousy run the following:

    GRANT IMPERSONATE ON LOGIN::[SnapShotCreator] To [DOMAIN\DOMAINGROUP]

    When I run the code to recreate the snapshot, I get

    Msg 916, Level 14, State 1, Line 1

    The server principal "SnapShotCreator" is not able to access the database "DATABASE1" under the current security context.

    What am I missing?

  • November 26, 2014 at 3:31 pm

    #1761178

    The question is not very clear, but EXECUTE AS is a non-starter. Sign the procedure with a certificate and create a login from the certificate and grant that login the permissions required. If the procedure is in a user database, you will need the certificate in both places.

    For a detailed description of this technique, and why EXECUTE AS will not work for you, see this article on my web site:

    http://www.sommarskog.se/grantperm.html

    As for preventing users to drop "real" databases, this is logic you need to code in your procedure.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • January 15, 2015 at 10:24 pm

    #1770638

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply