MCITP and stored procedure permissions

Discussion in 'MCITP' started by Darrilgibson@gmail.com, Jan 25, 2008.

  1. Guest

    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/2008/01/mcitp-and-stored-procedure-permissions.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)
    , Jan 25, 2008
    #1
    1. Advertising

  2. <> wrote in message
    news:...
    > 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-2008)

    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/profile/Lawrence.Garvin
    Lawrence Garvin, Feb 16, 2008
    #2
    1. Advertising

  3. BTA Guest

    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:...
    > > 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-2008)
    >
    > 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/profile/Lawrence.Garvin
    >
    >
    BTA, Apr 26, 2008
    #3
  4. BTA Guest

    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:...
    > > 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-2008)
    >
    > 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/profile/Lawrence.Garvin
    >
    >
    BTA, Apr 26, 2008
    #4
  5. "BTA" <> wrote in message
    news:...

    > "Lawrence Garvin" wrote:
    >
    >> <> wrote in message
    >> news:...


    >> > 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
    .. 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-2008)

    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/profile/Lawrence.Garvin
    Lawrence Garvin [MVP], Apr 30, 2008
    #5
  6. Guest

    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]" <>
    wrote:
    > "BTA" <> wrote in message
    >
    > news:...
    >
    >
    >
    >
    >
    > > "Lawrence Garvin" wrote:

    >
    > >> <> wrote in message
    > >>news:....
    > >> > 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
    >     .. 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-2008)
    >
    > 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/profile/Lawrence.Garvin- Hide quoted text -
    >
    > - Show quoted text -
    , Jun 7, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. iixv
    Replies:
    0
    Views:
    815
  2. fokker
    Replies:
    7
    Views:
    1,122
    Fokker
    Sep 11, 2003
  3. Robert
    Replies:
    7
    Views:
    2,368
    Johnretlaw
    Dec 20, 2008
  4. Polo

    MCITP: EA and MCITP: SA

    Polo, Jul 20, 2009, in forum: MCITP
    Replies:
    2
    Views:
    5,375
    Victor Asuquo
    Jul 31, 2009
  5. sailesh
    Replies:
    0
    Views:
    1,925
    sailesh
    Mar 30, 2012
Loading...

Share This Page