January 31, 2018 at 8:30 pm
Comments posted to this topic are about the item Viewing Plans
February 1, 2018 at 1:13 am
Nice, easy one, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
February 1, 2018 at 1:28 am
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.
February 1, 2018 at 1:30 am
GRANT VIEW SHOWPLAN to JoeDevMsg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VIEW'.
Instead, the following codes run both right: GRANT CONTROL to JoeDev
orGRANT SHOWPLAN to JoeDev
February 1, 2018 at 1:54 am
Carlo Romagnano - Thursday, February 1, 2018 1:30 AMRunning the following code raises an error:GRANT VIEW SHOWPLAN to JoeDevMsg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VIEW'.Instead, the following codes run both right:
GRANT CONTROL to JoeDev
orGRANT 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”
February 1, 2018 at 7:46 am
Corrected the answers. Not sure what I was thinking while typing.
February 1, 2018 at 7:58 am
Steve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AMCorrected 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!
February 1, 2018 at 9:20 am
Carlo Romagnano - Thursday, February 1, 2018 7:58 AMSteve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AMCorrected 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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
February 1, 2018 at 10:15 am
Solomon Rutzky - Thursday, February 1, 2018 9:20 AMCarlo Romagnano - Thursday, February 1, 2018 7:58 AMSteve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AMCorrected 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
February 1, 2018 at 10:23 am
Carlo Romagnano - Thursday, February 1, 2018 10:15 AMSolomon Rutzky - Thursday, February 1, 2018 9:20 AMCarlo Romagnano - Thursday, February 1, 2018 7:58 AMSteve Jones - SSC Editor - Thursday, February 1, 2018 7:46 AMCorrected 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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
February 1, 2018 at 8:31 pm
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.
February 4, 2018 at 8:03 pm
Solomon Rutzky - Thursday, February 1, 2018 9:20 AMHi 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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply