Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Impersonation in an Execute As statement Expand / Collapse
Author
Message
Posted Thursday, May 15, 2008 12:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, Visits: 165
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

Post #501037
Posted Thursday, May 15, 2008 3:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 19, 2010 6:47 AM
Points: 100, Visits: 130
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.
Post #501125
Posted Thursday, May 15, 2008 5:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
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
Post #501160
Posted Thursday, May 15, 2008 5:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:10 PM
Points: 2,669, Visits: 781
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
Post #501182
Posted Thursday, May 15, 2008 5:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 19, 2010 6:47 AM
Points: 100, Visits: 130
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...
Post #501192
Posted Thursday, May 15, 2008 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 10,214, Visits: 13,161
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #501326
Posted Thursday, May 15, 2008 9:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:02 AM
Points: 506, Visits: 242
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)




Post #501414
Posted Thursday, May 15, 2008 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 PM
Points: 5,333, Visits: 25,264
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

Before posting a performance problem please read
Post #501435
Posted Thursday, May 15, 2008 12:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:10 PM
Points: 2,669, Visits: 781
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
Post #501565
Posted Thursday, May 15, 2008 1:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, Visits: 57
Have mulitple answers always been allowed? I must be asleep at the wheel.


Post #501608
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse