Article
10/04/2012

Showplan Security

Showplan execution plan information can be produced by various ways. You can use Transact-SQL SET statement options, SQL Server Profiler event classes, or you can query the dynamic management function sys.dm_exec_query_plan. Each method requires a different set of permissions, which are described in the following sections. For more information about how the SHOWPLAN permission is checked for Transact-SQL batches, see SHOWPLAN Permission and Transact-SQL Batches.

Note

When a SQL Server database compatibility level is set to 80 by using the sp_dbcmptlevel stored procedure the current SHOWPLAN permission still applies. Setting the compatibility level to 80 does not produce the Showplan permissions behavior of Microsoft SQL Server 2000.

About the SHOWPLAN Permission

To produce execution plan output by using most Showplan Transact-SQL SET options, users must have:

The SHOWPLAN permission on the databases that contain objects referred to in the Transact-SQL statement, such as views, stored procedures, or user-defined functions.

The appropriate permission to execute the Transact-SQL statement itself.

Security Note
Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information. 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.

Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information.

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.

The SHOWPLAN permission is a database-level permission which:

Can be granted, denied, or revoked only by the following users:
- Members of the sysadmin fixed server role. By default, all members of this fixed server role have the SHOWPLAN permission on all of the databases on the server.
- Members of the dbcreator fixed server role for databases they create and thus own. By default, all members of this fixed server role have the SHOWPLAN permission on databases they create and thus own.
- Members of the db_owners fixed database role for databases they own. By default, all members of this fixed database role have the SHOWPLAN permission on databases they own.
Supports ownership chaining. When the ownership chain is broken, the permission is checked again at the node where the break occurred. However, because the SHOWPLAN permission is a database-level permission, this check only occurs when queries reference objects in two or more databases. For more information about ownership chaining, see Ownership Chains.

For information about the syntax used to grant, deny, or revoke the SHOWPLAN permission, see Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission.

Example

If User1 has CREATE TABLE, INSERT, and SELECT permissions, and he creates table T (he is the table owner) in database D, inserts rows into the table, and then writes a SELECT query on the table, the query executes successfully. However, User1 is not able to generate a Showplan until he is granted the SHOWPLAN permission on database D.

Caveat

In the previous example, suppose that database D contains view V for which User1 has SELECT permission. After User1 has been granted the SHOWPLAN permission for D, although he does not own V, he can still generate a Showplan on a query posed to V. This Showplan enables him to see the view definition for V, including the tables and views on which V is based. However, if V contains an object, such as a table, that is owned by User1 and which exists in a different database, D2, and User1 is not the owner of D2, the SHOWPLAN permission on D2 is checked and required.

Permissions Required to Use Showplan SET Options

The permissions required to use the various Showplan SET statement options are listed in the following table:

Showplan SET options	Permissions required
SET SHOWPLAN_XML ON SET SHOWPLAN_ALL ON SET SHOWPLAN_TEXT ON	For SELECT, INSERT, UPDATE, DELETE, EXEC stored_prodedure, and EXEC user_defined_function statements, the following permissions are required to produce a Showplan: Appropriate permissions to execute the Transact-SQL statements. SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements, such as tables, views, and so on. For all other statements, such as DDL, USE database_name, SET, DECLARE, dynamic Transact-SQL, and so on, only the appropriate permissions to execute the Transact-SQL statement are needed. For more information, see SHOWPLAN Permission and Transact-SQL Batches.
SET STATISTICS XML ON SET STATISTICS PROFILE ON	Appropriate permissions to execute the Transact-SQL statements. SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements. For Transact-SQL statements that do not produce STATISTICS PROFILE or STATISTICS XML result sets, only the appropriate permissions to execute the Transact-SQL statements are required. For Transact-SQL statements that do produce STATISTICS PROFILE or STATISTICS XML result sets, checks for both the Transact-SQL statement execution permission and the SHOWPLAN permission must succeed, or the Transact-SQL statement execution is aborted and no Showplan information is generated. For information about which Transact-SQL statements produce Showplan information, see Transact-SQL Statements That Produce Showplans.
SET STATISTICS TIME SET STATISTICS IO	Appropriate permissions to execute the Transact-SQL statements. Neither of these SET statement options check for or require the SHOWPLAN permission.

SET SHOWPLAN_XML ON

SET SHOWPLAN_ALL ON

SET SHOWPLAN_TEXT ON

For SELECT, INSERT, UPDATE, DELETE, EXEC stored_prodedure, and EXEC user_defined_function statements, the following permissions are required to produce a Showplan:

Appropriate permissions to execute the Transact-SQL statements.
SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements, such as tables, views, and so on.

For all other statements, such as DDL, USE database_name, SET, DECLARE, dynamic Transact-SQL, and so on, only the appropriate permissions to execute the Transact-SQL statement are needed. For more information, see SHOWPLAN Permission and Transact-SQL Batches.

SET STATISTICS XML ON

SET STATISTICS PROFILE ON

Appropriate permissions to execute the Transact-SQL statements.
SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements.

For Transact-SQL statements that do not produce STATISTICS PROFILE or STATISTICS XML result sets, only the appropriate permissions to execute the Transact-SQL statements are required. For Transact-SQL statements that do produce STATISTICS PROFILE or STATISTICS XML result sets, checks for both the Transact-SQL statement execution permission and the SHOWPLAN permission must succeed, or the Transact-SQL statement execution is aborted and no Showplan information is generated. For information about which Transact-SQL statements produce Showplan information, see Transact-SQL Statements That Produce Showplans.

SET STATISTICS TIME

SET STATISTICS IO

Appropriate permissions to execute the Transact-SQL statements.

Neither of these SET statement options check for or require the SHOWPLAN permission.

When Is the SHOWPLAN Permission Checked?

The SHOWPLAN permission is checked when a Transact-SQL statement or batch executes and Showplan information is generated. The check does not occur when a Showplan SET option is set to ON.

Note

The context database for a Transact-SQL batch is set by using a USE <database_name> statement. The SHOWPLAN permission is not checked on USE <database_name> statements and is not checked on the context database.

For more information about the Showplan SET statement options, see the following topics:

Permissions Required to Display Graphical Execution Plans by Using SQL Server Management Studio

The permissions required to display graphical execution plans in SQL Server Management Studio are listed in the following table:

Management Studio Execution Plan option	Permissions Required
Display Estimated Execution Plan	Requires the same permissions needed to use the SHOWPLAN_XML SET statement option
Include Actual Execution Plan	Requires the same permissions needed to use the STATISTICS XML SET statement option

For more information, see Displaying Graphical Execution Plans (SQL Server Management Studio).

Permissions Required to Display Execution Plans by Using SQL Server Profiler Event Classes

To display execution plans by using SQL Server Profiler event classes, users must be a member of the sysadmin fixed server role, or be granted the ALTER TRACE permission. The SHOWPLAN permission is not checked nor is it required.

For more information, see Displaying Execution Plans by Using SQL Server Profiler Event Classes.

Permissions Required to Display Execution Plans by Using the sys.dm_exec_query_plan Dynamic Management Function

To display execution plans by using the sys.dm_exec_query_plan dynamic management function, users must be granted the VIEW SERVER STATE permission only.

For more information, see sys.dm_exec_query_plan (Transact-SQL).

Share via