Excellent insight Lawrence,
My intent with the question was just to reinforce the concept of
permission chaining. If granted EXEC permission on a stored
procedure, you don't need permission on the underlying table. It's
tempting to say that you need UPDATE and INSERT (and even SELECT)
permissions, but as long as you have EXEC permission that's all you
need.
Lawrence is accurate when he says that the stored procedure must have
been created correctly to allow you to execute it for the Widgets
table. (This is one of the reasons that some of the Microsoft exam
test questions become so long. The test writer is accounting for
every possibility that sophisticated exam takers like Lawrence will
know about and question.) While Microsoft has in the past often used
"trick" questions, they seem to be getting away from that and
questions are more straight forward. What they seem to be asking, is
exactly what they are asking.
Q. You've just developed a stored procedure that can be used to
update
data in the Widgets table or add new Widgets to the Widgets table if
they don't exist. What permissions are required for someone to use
the
stored procedure? (Choose all that apply).
A. SELECT permission on the Widgets table.
B. UPDATE permission on the Widgets table.
C. INSERT permission on the Widgets table.
D. EXEC permission on the stored procedure.
Answer: D
Darril Gibson
MCT, MCDBA, MCITP, MCSE, MCSD
http://mcitpsuccess.com/
http://mcitpsuccess.blogspot.com
Author, MCITP SQL Server 2005 Database Administration All-in-One Exam
Guide (Exams 70-431, 70-443, & 70-444)
Author, MCITP SQL Server 2005 Database Developer All-in-One Exam
Guide (Exams 70-431, 70-441, & 70-442)
On Apr 29, 11:50=A0pm, "Lawrence Garvin [MVP]" <ons...@[EMAIL PROTECTED]
>
wrote:
> "BTA" <B...@[EMAIL PROTECTED]
> wrote in message
>
> news:54F3CD95-3948-4804-A9B5-EF3ACAA2A6FC@[EMAIL PROTECTED]
>
>
>
>
>
> > "Lawrence Garvin" wrote:
>
> >> <Darrilgib...@[EMAIL PROTECTED]
> wrote in message
>
>>news:5bd84905-5c2c-485e-a07b-30add9aa5bce@[EMAIL PROTECTED]
> >> > If you're getting ready to take an MCITP exam on SQL Server 2005
(suc=
h
> >> > as 70-441, 70-442, 70-444), you'll do better if you have a solid
> >> > understanding of stored procedure permissions.
>
> >> > For example, what's the answer to this question?
>
> >> > Q. You've just developed a stored procedure that can be used to
updat=
e
> >> > data in the Widgets table or add new Widgets to the Widgets table
if
> >> > they don't exist. What permissions are required for someone to use
th=
e
> >> > stored procedure? (Choose all that apply).
>
> >> > A. SELECT permission on the Widgets table.
> >> > B. UPDATE permission on the Widgets table.
> >> > C. INSERT permission on the Widgets table.
> >> > D. EXEC permission on the stored procedure.
>
> >> The answer: It depends.
> > I also thought that with the exec permission on the stored procedure ,
s=
ql
> > would allow to update and insert into the table, so we need to have A
an=
d
> > D,
> > Is that correct?
>
> In general, if one has EXEC permissions on a stored proc, they'll be
able =
to
> perform all functionality defined in the stored proc.
>
> However, this is not guaranteed.
>
> > How can we enter the answer 'it depends' on =A0this case?
>
> The point of my answer, aside from being a tongue-in-cheek consultant's
> joke, is that the question, as presented, contains insufficient
informatio=
n
> to accurately select from the options given.
>
> For example, first the stored procedure has two functions:
> =A0 =A0 [a] .. update data in the Widgets table
> =A0 =A0 [b] .. insert data into the Widgets table
>
> So, it would seem that UPDATE and INSERT permissions would be required.
>
> Also, implied in the text, but not explicitly spelled out is that the
proc=
> also performs the insert "...if the data doesn't already exist.." which
al=
so
> implies that one needs SELECT permissions on the table in order to
determi=
ne
> if the INSERT is required.
>
> But the question fails to address the reality that one really only needs
> EXEC permissions on the stored procedure, provided that the stored
procedu=
re
> is created with the proper EXECUTE AS parameter, and since this is an
MCIT=
P
> question, we can also conclude that it's a SQL 2005 question, which
means
> that the EXECUTE AS option is a valid consideration.
>
> Truth be told, even in SQL 2000 one only needs EXECUTE permissions on a
> stored procedure, if the proc is coded to select/insert/update to any
othe=
r
> table.
>
> Of course, this is all dependent on whether you also own the Widgets
table=
> being accessed by this procedure, which allows for an unbroken owner****p
> chain between the procedure and the table. If =3Dyou=3D don't own the
Widg=
ets
> table, then the user of your procedure *must* have all four permissions
in=
> order to use the procedure.
>
> But, even more significantly, if the stored procedure had been coded to
> EXECUTE AS CALLER, then the correct answer(s) are, again, all four,
> (presuming, again, that the proc makes use of SELECT in order to qualify
t=
he
> requirement to execute the INSERT), because the user of the procedure
must=
> then have permissions on the Widgets table, regardless of who actually
own=
s
> the table.
>
> Unfortunately, the question instructs us to "Choose all that apply",
which=
> then implies that answer 'D' as the only answer cannot be the correct
> answer, even though it really is the *correct* answer in most normal
> cir***stances; the only other possible correct answer being that all
four
> permissions are required.
>
> So.... it depends. :-)
>
> Do you want the *CORRECT* answer...
>
> =A0 =A0 Or do you want the correct answer for that particular question?
>
> [I'm not sure if Darril took that question from his book, or from
another
> source, but personally I think the question needs some clarification.]
>
> There is an excellent discussion from SSBOL on database owner****p
chains,
> which is what this question is attempting to test:
> =A0 =A0
ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/udb9/html/=
762249ee-=AD881a-4c3e-b8c0-3a9475039aca.htm
>
> --
> Lawrence Garvin, M.S., MCITP, MCBMSP, MCTS(x4), MCP
> Senior Data Architect, APQC, Houston, Texas
> Microsoft MVP - Software Distribution (2005-2008)
>
> MS WSUS Website:http://www.microsoft.com/wsus
> My
Websites:http://www.onsitechsolutions.com;http://wsusinfo.onsitechsolut=
ions.com
> My MVP Profile:http://mvp.sup****t.microsoft.com/profile/Lawrence.Garvin-
H=
ide quoted text -
>
> - Show quoted text -
|
