SHOWPLAN documentation

Question

SHOWPLAN documentation

as_1234

SSCrazy

Points: 2859
More actions
June 20, 2024 at 1:01 pm

#4430006

The most recent documentation I can find is this https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189602(v=sql.105) which is showing as retired. Does anyone know if there is a newer version which applies to 2016?
Apologies for asking for assistance with searching. I have searched extensively and not been able to find it.
I'm guessing the content is still accurate for 2016 but I'd rather see the information in documentation rather than rely on my testing if possible.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

Is this what you are looking for?:

https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver15

as_1234 SSCrazy Points: 2859 More actions · Answer 2

Thanks for your help and apologies for taking a few days to reply.

I am looking for the permissions information regarding showplan rather than the operators. The information on the risks of giving users showplan permissions, what permissions are required for the various showplan set options e.t.c

Thanks

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

that link kind of says it all - but it is a bit over the top in my opinion.

first thing - separate the 3 permissions mentioned (SHOWPLAN, ALTER TRACE and View Server State)

Showplan on its own does NOT grant permissions to the user to see anything the user can't see already - and it does not grant them any other permission - so if user has permission to do select on tables they will be able to do a Showplan on the queries that do a select on that table - but if user does not have Update permission on the same table he will be unable to do a explain plan on a query that does a update of said table (evne if query is not executed (and I do hate restriction this by the way))

even the example they mention

For example, consider the following query:

SELECT COUNT(*)

FROM table_1

WHERE column_1 < 10

If a malicious user produces Showplan output for a set of queries like this example, and replaces the value "10" in the predicate with different constants each time, the user could infer an approximate data distribution of the column values for column_1 in table_1 by reading the estimated row counts.

if user is able to do the select above they do NOT need showplan to figure out the data distribution - it just means they will need to do a few more selects, or even a different select in order to achieve that same result.

View Server State - should only be restricted if

1 - server has databases for different business units/entities and data/object definition should be restricted between the different them.

2 - server has masked/encrypted columns which should not be visible for majority of users even if point below applies

however if a server only contains databases for which all users that "may need" explain plan, all read have access to them, this is not really a risk anymore - as users will be able to see the data regardless (caveats may apply)

ALTER TRACE - Should not be given to users - this permission can allow a disgruntled user to bring down the server (by overloading server and potentially filling up the drives where traces can be written to)

only DBA's and potentially selected IT Production support people should have this - and only after required training.

Caveats for the View Server State

Certain type of servers should neve have this granted to anyone other than a DBA

HR/Payroll
Medical related databases
and databases with highly regulated data

but again these type of databases should not have users querying them directly anyway - so need to grant any permission should not even be needed.

and finally - what type of users are asking for that permission and what type of databases (assuming this is Prod - non prod environments should be more relaxed, as they should NEVER EVER contain information that can't be seen by everyone on their teams)

as_1234 SSCrazy Points: 2859 More actions · Answer 4

Thanks for your reply and the detailed information.