MCITP and stored procedure permissions

If you're getting ready to take an MCITP exam on SQL Server 2005 (such
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 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.

For the answer and an explanation, check out the MCITP blog at:
http://mcitpsuccess.blogspot.com/200...rmissions.html

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)

Darrilgibson@gmail.com

<> wrote in message
news:5bd84905-5c2c-485e-a07b-...
> If you're getting ready to take an MCITP exam on SQL Server 2005 (such
> 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 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.

The answer: It depends.


<g>


--
Lawrence Garvin, M.S., MCBMSP, MCTS, MCP
Senior Data Architect, APQC, Houston, Texas
Microsoft MVP - Software Distribution (2005-200

MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/pro...awrence.Garvin

Lawrence Garvin

OK, but it depends is not in the choices, so what to do to pass the exam?
Thanks.

"Lawrence Garvin" wrote:

> <> wrote in message
> news:5bd84905-5c2c-485e-a07b-...
> > If you're getting ready to take an MCITP exam on SQL Server 2005 (such
> > 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 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.
>
> The answer: It depends.
>
>
> <g>
>
>
> --
> Lawrence Garvin, M.S., MCBMSP, MCTS, MCP
> Senior Data Architect, APQC, Houston, Texas
> Microsoft MVP - Software Distribution (2005-200
>
> MS WSUS Website: http://www.microsoft.com/wsus
> My Websites: http://www.onsitechsolutions.com;
> http://wsusinfo.onsitechsolutions.com
> My MVP Profile: http://mvp.support.microsoft.com/pro...awrence.Garvin
>
>

BTA

I also thought that with the exec permission on the stored procedure , sql
would allow to update and insert into the table, so we need to have A and D,
Is that correct? How can we enter the answer 'it depends' on this case?

Thanks for any clarification.

"Lawrence Garvin" wrote:

> <> wrote in message
> news:5bd84905-5c2c-485e-a07b-...
> > If you're getting ready to take an MCITP exam on SQL Server 2005 (such
> > 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 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.
>
> The answer: It depends.
>
>
> <g>
>
>
> --
> Lawrence Garvin, M.S., MCBMSP, MCTS, MCP
> Senior Data Architect, APQC, Houston, Texas
> Microsoft MVP - Software Distribution (2005-200
>
> MS WSUS Website: http://www.microsoft.com/wsus
> My Websites: http://www.onsitechsolutions.com;
> http://wsusinfo.onsitechsolutions.com
> My MVP Profile: http://mvp.support.microsoft.com/pro...awrence.Garvin
>
>

BTA

"BTA" <> wrote in message
news:54F3CD95-3948-4804-A9B5-...

> "Lawrence Garvin" wrote:
>
>> <> wrote in message
>> news:5bd84905-5c2c-485e-a07b-...

>> > If you're getting ready to take an MCITP exam on SQL Server 2005 (such
>> > 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 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.
>>
>> The answer: It depends.


> I also thought that with the exec permission on the stored procedure , sql
> would allow to update and insert into the table, so we need to have A and
> 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 this case?

The point of my answer, aside from being a tongue-in-cheek consultant's
joke, is that the question, as presented, contains insufficient information
to accurately select from the options given.

For example, first the stored procedure has two functions:
[a] .. update data in the Widgets table
[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 also
implies that one needs SELECT permissions on the table in order to determine
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 procedure
is created with the proper EXECUTE AS parameter, and since this is an MCITP
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 other
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 ownership
chain between the procedure and the table. If =you= don't own the Widgets
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 the
requirement to execute the INSERT), because the user of the procedure must
then have permissions on the Widgets table, regardless of who actually owns
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
circumstances; the only other possible correct answer being that all four
permissions are required.


So.... it depends.


Do you want the *CORRECT* answer...

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 ownership chains,
which is what this question is attempting to test:
ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9475039aca.htm


--
Lawrence Garvin, M.S., MCITP, MCBMSP, MCTS(x4), MCP
Senior Data Architect, APQC, Houston, Texas
Microsoft MVP - Software Distribution (2005-200

MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/pro...awrence.Garvin

Lawrence Garvin [MVP]

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*pm, "Lawrence Garvin [MVP]" <ons...@postalias.news>
wrote:
> "BTA" <B...@discussions.microsoft.com> wrote in message
>
> news:54F3CD95-3948-4804-A9B5-...
>
>
>
>
>
> > "Lawrence Garvin" wrote:
>
> >> <Darrilgib...@gmail.com> wrote in message
> >>news:5bd84905-5c2c-485e-a07b-....
> >> > If you're getting ready to take an MCITP exam on SQL Server 2005 (such
> >> > 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 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.
>
> >> The answer: It depends.
> > I also thought that with the exec permission on the stored procedure , sql
> > would allow to update and insert into the table, so we need to have A and
> > 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 *this case?
>
> The point of my answer, aside from being a tongue-in-cheek consultant's
> joke, is that the question, as presented, contains insufficient information
> to accurately select from the options given.
>
> For example, first the stored procedure has two functions:
> * * [a] .. update data in the Widgets table
> * * [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 also
> implies that one needs SELECT permissions on the table in order to determine
> 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 procedure
> is created with the proper EXECUTE AS parameter, and since this is an MCITP
> 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 other
> 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 ownership
> chain between the procedure and the table. If =you= don't own the Widgets
> 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 the
> requirement to execute the INSERT), because the user of the procedure must
> then have permissions on the Widgets table, regardless of who actually owns
> 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
> circumstances; the only other possible correct answer being that all four
> permissions are required.
>
> So.... it depends.
>
> Do you want the *CORRECT* answer...
>
> * * 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 ownership chains,
> which is what this question is attempting to test:
> * * ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/udb9/html/762249ee-*881a-4c3e-b8c0-3a9475039aca.htm
>
> --
> Lawrence Garvin, M.S., MCITP, MCBMSP, MCTS(x4), MCP
> Senior Data Architect, APQC, Houston, Texas
> Microsoft MVP - Software Distribution (2005-200
>
> MS WSUS Website:http://www.microsoft.com/wsus
> My Websites:http://www.onsitechsolutions.com;htt...hsolutions.com
> My MVP Profile:http://mvp.support.microsoft.com/pro...wrence.Garvin- Hide quoted text -
>
> - Show quoted text -

Darrilgibson@gmail.com