Viewing Plans

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716172

    Comments posted to this topic are about the item Viewing Plans

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71438

    Nice, easy one, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Gareth Davison

    Right there with Babe

    Points: 723

    The correct answer is invalid syntax. It should be

    GRANT SHOWPLAN to JoeDev

    Instead of

    GRANT VIEW SHOWPLAN to JoeDev

    I am perhaps being a touch pedantic, but it did very nearly cause me to rule out this answer since it is not correct syntax. 

  • Carlo Romagnano

    SSC-Insane

    Points: 21807

    Running the following code raises an error:
    GRANT VIEW SHOWPLAN to JoeDev

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'VIEW'.

    Instead, the following codes run both right:
    GRANT CONTROL to JoeDev
    or
    GRANT SHOWPLAN to JoeDev

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71438

    Carlo Romagnano - Thursday, February 1, 2018 1:30 AM

    Running the following code raises an error:
    GRANT VIEW SHOWPLAN to JoeDev

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'VIEW'.

    Instead, the following codes run both right:
    GRANT CONTROL to JoeDev
    or
    GRANT SHOWPLAN to JoeDev

    Good grief - in such a rush i totally missed the "VIEW" - saw the showplan option, selected & submitted.
    fortunately for me it was the correct answer...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716172

    Corrected the answers. Not sure what I was thinking while typing.

  • Carlo Romagnano

    SSC-Insane

    Points: 21807

    Steve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AM

    Corrected the answers. Not sure what I was thinking while typing.

    ... and points back!
    This code allows a user to show the plan:

    GRANT CONTROL to JoeDevJoeDev  
    So, that was a correct answer!

  • Solomon Rutzky

    SSCoach

    Points: 16135

    Carlo Romagnano - Thursday, February 1, 2018 7:58 AM

    Steve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AM

    Corrected the answers. Not sure what I was thinking while typing.

    ... and points back!
    This code allows a user to show the plan:

    GRANT CONTROL to JoeDevJoeDev  
    So, that was a correct answer!

    Hi Carlo. Yes, "GRANT CONTROL TO [user_name];", or even "GRANT CONTROL ON DATABASE::[database_name] TO [user_name];", does allow for viewing the execution plan. But, that wasn't one of the options. The option in the question was "GRANT CONTROL ON [view] TO ;", and that does not work. Object-level and Schema-level CONTROL does not allow for viewing plans. Only the Database-level permissions of either SHOWPLAN or CONTROL will do it (well, or being in one of a few Roles, but those were not options either).

    Steve, would it be possible to update the URL in the answer explanation to be:

    https://technet.microsoft.com/en-us/library/ms175117(v=sql.105).aspx

    ? It is the same URL, but cleaned up. The ( and ) are not encoded, and there is no reason to ever copy the "?f=255&MSPPError=-2147217396" part of those MSDN / TechNet URLs (which only show up sometimes anyway). Thanks 🙂  

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Carlo Romagnano

    SSC-Insane

    Points: 21807

    Solomon Rutzky - Thursday, February 1, 2018 9:20 AM

    Carlo Romagnano - Thursday, February 1, 2018 7:58 AM

    Steve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AM

    Corrected the answers. Not sure what I was thinking while typing.

    ... and points back!
    This code allows a user to show the plan:

    GRANT CONTROL to JoeDevJoeDev  
    So, that was a correct answer!

    Hi Carlo. Yes, "GRANT CONTROL TO [user_name];", or even "GRANT CONTROL ON DATABASE::[database_name] TO [user_name];", does allow for viewing the execution plan. But, that wasn't one of the options. The option in the question was "GRANT CONTROL ON [view] TO ;", and that does not work. Object-level and Schema-level CONTROL does not allow for viewing plans. Only the Database-level permissions of either SHOWPLAN or CONTROL will do it (well, or being in one of a few Roles, but those were not options either).

    Steve, would it be possible to update the URL in the answer explanation to be:

    https://technet.microsoft.com/en-us/library/ms175117(v=sql.105).aspx

    ? It is the same URL, but cleaned up. The ( and ) are not encoded, and there is no reason to ever copy the "?f=255&MSPPError=-2147217396" part of those MSDN / TechNet URLs (which only show up sometimes anyway). Thanks 🙂  

    Take care, Solomon..

    This code runs good:

    grant control on vw_myView to public

  • Solomon Rutzky

    SSCoach

    Points: 16135

    Carlo Romagnano - Thursday, February 1, 2018 10:15 AM

    Solomon Rutzky - Thursday, February 1, 2018 9:20 AM

    Carlo Romagnano - Thursday, February 1, 2018 7:58 AM

    Steve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AM

    Corrected the answers. Not sure what I was thinking while typing.

    ... and points back!
    This code allows a user to show the plan:

    GRANT CONTROL to JoeDevJoeDev  
    So, that was a correct answer!

    Hi Carlo. Yes, "GRANT CONTROL TO [user_name];", or even "GRANT CONTROL ON DATABASE::[database_name] TO [user_name];", does allow for viewing the execution plan. But, that wasn't one of the options. The option in the question was "GRANT CONTROL ON [view] TO ;", and that does not work. Object-level and Schema-level CONTROL does not allow for viewing plans. Only the Database-level permissions of either SHOWPLAN or CONTROL will do it (well, or being in one of a few Roles, but those were not options either).

    Steve, would it be possible to update the URL in the answer explanation to be:

    https://technet.microsoft.com/en-us/library/ms175117(v=sql.105).aspx

    ? It is the same URL, but cleaned up. The ( and ) are not encoded, and there is no reason to ever copy the "?f=255&MSPPError=-2147217396" part of those MSDN / TechNet URLs (which only show up sometimes anyway). Thanks 🙂  

    Take care, Solomon..

    This code runs good:

    grant control on vw_myView to public

    Hi again. Yes, that statement will execute without error. However, it won't allow for viewing execution plans. Object-level CONTROL does not grant that permission, nor does Schema-level CONTROL. Only Database-level works for granting permission to view the execution plan.

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • t.ovod-everett

    Ten Centuries

    Points: 1310

    I think it's important to point out why SHOWPLAN cannot be granted at the object level.  The critical point is that views don't have plans!  A view is not a query - it can be part of a query, but only queries have plans.

    SELECT * FROM MyView may have a totally different plan from SELECT PrimaryKeyColumn FROM MyView.  For instance, if there are any non-clustered indexes on the table, the later should pick the smallest non-clustered index and use that to dump out a list of PrimaryKeyColumn values because that results in less I/O than querying the clustered index.

    As a result, it doesn't make sense to grant SHOWPLAN on a view - it only makes sense to grant it in the context of the as-of-yet unsubmitted query, which means the grant has to be at a broader level.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716172

    Solomon Rutzky - Thursday, February 1, 2018 9:20 AM

    Hi Carlo. Yes, "GRANT CONTROL TO [user_name];", or even "GRANT CONTROL ON DATABASE::[database_name] TO [user_name];", does allow for viewing the execution plan. But, that wasn't one of the options. The option in the question was "GRANT CONTROL ON [view] TO ;", and that does not work. Object-level and Schema-level CONTROL does not allow for viewing plans. Only the Database-level permissions of either SHOWPLAN or CONTROL will do it (well, or being in one of a few Roles, but those were not options either).

    Steve, would it be possible to update the URL in the answer explanation to be:

    https://technet.microsoft.com/en-us/library/ms175117(v=sql.105).aspx

    ? It is the same URL, but cleaned up. The ( and ) are not encoded, and there is no reason to ever copy the "?f=255&MSPPError=-2147217396" part of those MSDN / TechNet URLs (which only show up sometimes anyway). Thanks 🙂  

    Take care, Solomon..

    Done

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply