<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Keith LaMay
»
Impersonation in an Execute As statement
17 posts, Page 1 of 2
1
2
»»
Impersonation in an Execute As statement
Rate Topic
Display Mode
Topic Options
Author
Message
Q -631159
Q -631159
Posted Thursday, May 15, 2008 12:10 AM
SSC 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
davidthegray
davidthegray
Posted Thursday, May 15, 2008 3:29 AM
SSC-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
Derek Dongray
Derek Dongray
Posted Thursday, May 15, 2008 5:02 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342,
Visits: 1,946
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
Jamie Longstreet-481950
Jamie Longstreet-481950
Posted Thursday, May 15, 2008 5:36 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 2,440,
Visits: 714
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
davidthegray
davidthegray
Posted Thursday, May 15, 2008 5:46 AM
SSC-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
Jack Corbett
Jack Corbett
Posted Thursday, May 15, 2008 7:59 AM
SSChampion
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571,
Visits: 11,871
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
joshcsmith13
joshcsmith13
Posted Thursday, May 15, 2008 9:07 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:57 AM
Points: 502,
Visits: 212
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
bitbucket-25253
bitbucket-25253
Posted Thursday, May 15, 2008 9:25 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 5,102,
Visits: 20,207
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
Jamie Longstreet-481950
Jamie Longstreet-481950
Posted Thursday, May 15, 2008 12:18 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 2,440,
Visits: 714
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
TDuffy
TDuffy
Posted Thursday, May 15, 2008 1:42 PM
Ten 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 »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
