Post reply

Execute AS writing to different DB

  • October 26, 2017 at 5:34 pm

    #333422

    Here's a problem I ran up against today on a SQL2008 instance.

    I have an existing Stored Procedure that uses EXECUTE AS to run as a SQL user with different permissions.  It works.

    I am adding code to add a record to a log table in a different database on the same instance the EXECUTE AS user has read & insert rights granted on that table.

    But when I run the procedure, I get an error saying the EXECUTE AS server principal cannot access the logging database under the current security context.  I tried changing the security settings, even temporarily made the EXECUTE AS login an sa, but it could still not cannot access the logging database under the current security context.

    I set this up successfully on a 2012 instance where it works as expected. I don't see any difference on how the EXECUTE AS login/user is set up on the two servers.  Do SQL2012 and 2008 behave differently here?

  • October 26, 2017 at 6:32 pm

    #1965438

    dan-572483 - Thursday, October 26, 2017 5:34 PM

    Here's a problem I ran up against today on a SQL2008 instance.

    I have an existing Stored Procedure that uses EXECUTE AS to run as a SQL user with different permissions.  It works.

    I am adding code to add a record to a log table in a different database on the same instance the EXECUTE AS user has read & insert rights granted on that table.

    But when I run the procedure, I get an error saying the EXECUTE AS server principal cannot access the logging database under the current security context.  I tried changing the security settings, even temporarily made the EXECUTE AS login an sa, but it could still not cannot access the logging database under the current security context.

    I set this up successfully on a 2012 instance where it works as expected. I don't see any difference on how the EXECUTE AS login/user is set up on the two servers.  Do SQL2012 and 2008 behave differently here?

    It's likely due to the database scope of impersonation. There are a few things that could be different between the two different databases on the different instances.
    Did you check the database owners? Is one of the databases set to trustworthy? Those would be my first guesses. This documentation explains it more, how to extend the scope of the impersonation:
    Extending Database Impersonation by Using EXECUTE AS

    Sue

  • October 27, 2017 at 3:03 pm

    #1965629

    The Trustworthy setting was it. Thank you.

  • October 27, 2017 at 7:09 pm

    #1965643

    dan-572483 - Friday, October 27, 2017 3:03 PM

    The Trustworthy setting was it. Thank you.

    Was it on or off?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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