February 28, 2018 at 3:25 am
showplan denied when sproc deployed using "WITH EXECUTE AS OWNER"
Msg 262, Level 14, State 4, Procedure spc_toDBQ_SLAB_Blocking, Line 80
SHOWPLAN permission denied in database 'MyDatabase'.
What has to be granted so this plan can be shown ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 28, 2018 at 2:42 pm
ALZDBA - Wednesday, February 28, 2018 3:25 AMshowplan denied when sproc deployed using "WITH EXECUTE AS OWNER"Msg 262, Level 14, State 4, Procedure spc_toDBQ_SLAB_Blocking, Line 80
SHOWPLAN permission denied in database 'MyDatabase'.
What has to be granted so this plan can be shown ?
Whatever security context represents the owner would have to have showplan permissions, which may not be a good idea.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2018 at 4:43 am
Even created and executed by a sysadmin, it does throws the same error.
Also tested grant showplan to public .... same error at showplan time 🙁
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 1, 2018 at 8:17 am
Have to wonder if there might not be a DENY in place somewhere with respect to SHOWPLAN permissions, such that anything less than a reversal of the DENY would allow it to happen. Which thus begs the question of whether there is ANYONE at your location that can see an execution plan...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2018 at 8:26 am
sure. if we remove the "execute as" clause, they can see the sqlplan.
However, the sproc is intended to be used by a user account that only has exec auth for a couple of given sprocs. Still the devs are interested in the actually used sqlplans.
Still figuring out a way without having to ommit the clause.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 1, 2018 at 8:41 am
Found some interesting statements here:
https://technet.microsoft.com/en-us/library/ms178086(v=sql.105).aspx
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply