SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Keith LaMay / Impersonation in an Execute As statement / Latest Posts

RE: Impersonation in an Execute As statement

Wellington Aquino — Tue, 27 May 2008 20:22:26 GMT

Happened exactly the same to me, cause I choosed 2 & 3. Simply, I'm going to check it out on the SQL Server, and I'll see what happens with the DBO/SYSADMIN issue over the EXECUTE AS command; which as far as I can see has been the hot topic here for most of us.

RE: Impersonation in an Execute As statement

Anipaul — Sun, 18 May 2008 10:05:03 GMT

A good one.........

RE: Impersonation in an Execute As statement

gurmeet.singh — Thu, 15 May 2008 23:16:58 GMT

I totally agree to that. For more info seeThe 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

RE: Impersonation in an Execute As statement

Simon Facer — Thu, 15 May 2008 15:56:40 GMT

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

RE: Impersonation in an Execute As statement

Jack Corbett — Thu, 15 May 2008 15:48:45 GMT

[quote][b]Simon Facer (5/15/2008)[/b][hr](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 ...[/quote]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.

RE: Impersonation in an Execute As statement

Jamie Longstreet-481950 — Thu, 15 May 2008 15:06:47 GMT

Thanks Simon. Makes sense now.

RE: Impersonation in an Execute As statement

Simon Facer — Thu, 15 May 2008 14:28:02 GMT

(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 ...

RE: Impersonation in an Execute As statement

TDuffy — Thu, 15 May 2008 13:42:49 GMT

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

RE: Impersonation in an Execute As statement

Jamie Longstreet-481950 — Thu, 15 May 2008 12:18:57 GMT

[quote]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.[/quote]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?

RE: Impersonation in an Execute As statement

bitbucket-25253 — Thu, 15 May 2008 09:25:53 GMT

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.

RE: Impersonation in an Execute As statement

joshcsmith13 — Thu, 15 May 2008 09:07:38 GMT

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)

RE: Impersonation in an Execute As statement

Jack Corbett — Thu, 15 May 2008 07:59:34 GMT

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.

RE: Impersonation in an Execute As statement

davidthegray — Thu, 15 May 2008 05:46:25 GMT

[quote][b]Derek Dongray (5/15/2008)[/b][hr][quote][b]davidthegray (5/15/2008)[/b][hr]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.[/quote]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)[/quote]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:

RE: Impersonation in an Execute As statement

Jamie Longstreet-481950 — Thu, 15 May 2008 05:36:35 GMT

[quote]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. [/quote]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?

RE: Impersonation in an Execute As statement

Derek Dongray — Thu, 15 May 2008 05:02:59 GMT

[quote][b]davidthegray (5/15/2008)[/b][hr]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.[/quote]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)

RE: Impersonation in an Execute As statement

davidthegray — Thu, 15 May 2008 03:29:30 GMT

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.

Impersonation in an Execute As statement

Q -631159 — Thu, 15 May 2008 00:10:28 GMT

Comments posted to this topic are about the item [B]Impersonation in an Execute As statement[/B]