Post reply

Impersonation in an Execute As statement

  • May 15, 2008 at 12:10 am

    #190531

    Comments posted to this topic are about the item Impersonation in an Execute As statement

    Q

    Please take a number. Now serving emergency 1,203,894

  • May 15, 2008 at 3:29 am

    #815715

    I wonder why, although I chose the correct answer (I doublechecked with the back button), it says "You are wrong". And the precentages show total much more than 100%. There must be something wrong with the system.

  • May 15, 2008 at 5:03 am

    #815744

    davidthegray (5/15/2008)

    I wonder why, although I chose the correct answer (I doublechecked with the back button), it says "You are wrong". And the precentages show total much more than 100%. There must be something wrong with the system.

    The question allows multiple answers. the 'correct' answer is [2] and [4]. You probably omitted the tick on "The scope is explicitly defined" (as I did :D)

    Derek

  • May 15, 2008 at 5:36 am

    #815764

    If the statement is called by a member of sysadmin, server-level impersonation is used. If the statement is called by an account that is dbo, database-level impersonation is used.

    If this statement is not correct, then how can impersonation work under the context of the sa and dbo? Is the meaning here implying that impersonation is not used when the context is dbo or sa? Or is it implying that only the objects that the sa has referenced can be impersonated and not the sa status? (Ditto I suspect with the dbo). If the latter is the case, then this particular case, where one has "absolute" power in the database [dbo] or on the server [sa], is considered an exception?

    Jamie

  • May 15, 2008 at 5:46 am

    #815772

    Derek Dongray (5/15/2008)

    davidthegray (5/15/2008)

    I wonder why, although I chose the correct answer (I doublechecked with the back button), it says "You are wrong". And the precentages show total much more than 100%. There must be something wrong with the system.

    The question allows multiple answers. the 'correct' answer is [2] and [4]. You probably omitted the tick on "The scope is explicitly defined" (as I did :D)

    Yes you are right! I didn't realize it was a multiple answers question and stopped after reading the second answer. This also explains the percentages. Next time I'll be more carefull...:w00t:

  • May 15, 2008 at 7:59 am

    #815886

    I don't disagree with the answers other than that to me Explicity means the programmer has to say REVERT and without a REVERT I would consider it to be implicit which ending the session or module without using REVERT.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 15, 2008 at 9:07 am

    #815966

    I think I agree with Jack. It's frustrating when I know the right answer(s), but get docked because of obscure semantics. REVERT and ending session will end the impersonation. (and scope is explicit)

  • May 15, 2008 at 9:25 am

    #815985

    From:

    http://msdn.microsoft.com/en-us/library/ms188315.aspx

    The scope of impersonation is explicitly defined in the statement.

    The specified principal is specified as a LOGIN, a server-level impersonation,

    or as a USER, a database-level impersonation.

    Thus making the 3rd answer a correct answer.

    So then an individual who is a sysadmin logging in would not be impersonateing anything at the server level contrary to what I understand the reference above to mean.

    Comments anyone - or any one care to explain it in very simple words that I can understand.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 15, 2008 at 12:18 pm

    #816090

    So then an individual who is a sysadmin logging in would not be impersonateing anything at the server level contrary to what I understand the reference above to mean.

    That's where the question goes into the "gray" territory. I need more pixels - can no longer see that dark spot on my gray screen!

    If I am correct, and I've given it some thought, the answer (the third one which I also chose), is not correct because without a login, nothing happens at the server level without "sa" and the actual password - ahem, if you are using SQL Authentication.

    What baffles me is that with Windows Authentication, one would assume Impersonation works at the database level ONLY with a login on the server and then the alternative, impersonation without a login, seems to be not implied by the answer given among the four. Either way, SQL Auth or Windows Auth, a login must be present on the server for any of it to work.

    So assuming the login exists, I am back to square one where at least half the answer is correct - dbo means dbo whether impersonating or not - but sysadmin might mean something else as the SA is not required to provide dbo on every database and impersonation should technically pick up the rights on the databases explicitly defined by the sa for that server and not "sa" rights. I doubt it means that you can provide someone with the ability to impersonate the sa and then proceed to create their own rights (or does it work this way???)

    I'm no more clear on this than now that I was when I discovered the answer was not a correct one. What are your thoughts?

    Jamie

  • May 15, 2008 at 1:42 pm

    #816124

    Have mulitple answers always been allowed? I must be asleep at the wheel.

  • May 15, 2008 at 2:28 pm

    #816136

    (1) The 'must' gives it away, you don't have to use REVERT or another EXECUTE AS statement to change your context back.

    (3) If you're an sa, you can impersonate a user with Execute As User, hence #3 is wrong - a member of the sysadmin role can use database level impersonation.

    (4) "Explicitly Defined" in terms of executing as a Login or User - Execute As Login gives you impersonation at the server level, i.e. all databases, Execute As User gives you impersonation at the database only level, you can't switch to another database.

    This one was pretty clear-cut ...

  • May 15, 2008 at 3:06 pm

    #816162

    Thanks Simon. Makes sense now.

    Jamie

  • May 15, 2008 at 3:48 pm

    #816186

    Simon Facer (5/15/2008)

    (4) "Explicitly Defined" in terms of executing as a Login or User - Execute As Login gives you impersonation at the server level, i.e. all databases, Execute As User gives you impersonation at the database only level, you can't switch to another database.

    This one was pretty clear-cut ...

    I guess it was clear cut if you defined scope as Server (Login) or Database(Level). The way I defined scope was how long the impersonation lasted. Similar to the scope of a variable in C# or VB, or that you had to explicitly REVERT.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 15, 2008 at 3:56 pm

    #816192

    Talking of VB or C# - I guess 'scope' is an 'overloaded' term ... different meaning depending on circumstance.

  • May 15, 2008 at 11:16 pm

    #816257

    I totally agree to that. For more info see

    The change in execution context remains in effect until one of the following occurs:

    1.Another EXECUTE AS statement is run.

    2.A REVERT statement is run.

    3.The session is dropped.

    http://msdn.microsoft.com/en-us/library/ms181362.aspx

Viewing 15 posts - 1 through 15 (of 16 total)

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