I recently had the need to allow non-sysadmin, non-db_owner users to use the graphcal execution plan in SQL2005 on a query using tempdb (temp table). Assuming this worked just like "SET SHOWPLAN_* ON" I granted the user Showplan on the DB in question.
To test, I created a user with access to only the DB in question & tested "SET SHOWPLAN_TEXT ON", "SET SHOWPLAN_XML ON" & "SET SHOWPLAN_ALL ON" with no issues. However, when I tried to use the graphical execution plan ("Include Actual Execution Plan" option on the query) it failed with the below error...
Msg 262, Level 14, State 4, Line 29
SHOWPLAN permission denied in database 'tempdb'.
So, for a "normal" user who uses the graphical execution plan it seems my only option is to explicitly add them to tempdb & grant Showplan permissions (this would entail adding them to model as well so their perms persist on startup when tempdb is recreated). The other option would be to grant Showplan perms to guest on model/tempdb which given the official & observed (for example) vulnerabilities associated with SHOWPLAN seems a little crazy.
I don't normally knock MS around too much, but I really dislike both these options. Basically I'm being forced to choose between a gaping security hole (the guest scenario), a smaller security hole + a (to me) bad practice of granting a user access to a system DB that they really shouldn't need OR denying the perms to a user who's actually trying to do right.
Subscribe to:
Post Comments (Atom)
I dislike this as well.
ReplyDeleteThe problem with adding that grant (either to the user or to guest) in the [model] database is that your user will automatically wind up in all subsequently created databases.
The other solution is sp_procoption, which also requires you to set the "scan for startup procs" option via sp_configure.
Both "solutions" are lame for their own reasons. It shouldn't be that difficult to setup a developer in a dev environment to be able to "do the right thing" and validate the execution plan of their *own* *code* where a #temptable is used.
I shouldn't have to resort to these kind of tricks, nor should I have to elevate someone to db_owner or sysadmin.
BTW, I also hate when textboxes for blogs don't allow copying/pasting or the use of arrow keys.