• Article

SHOWPLAN Permission and Transact-SQL Batches

A Transact-SQL batch consists of one or more statements. This topic describes how SQL Server checks the SHOWPLAN permission for various Showplan SET statement options when they are used with Transact-SQL batches.

Security noteSecurity 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.

Batches Containing USE or SET Statements

No SHOWPLAN permission checks are performed on either USE or SET statements for any of the Showplan SET options (SHOWPLAN_TEXT, SHOWPLAN_ALL, SHOWPLAN_XML, STATISTICS PROFILE, or STATISTICS XML). For SHOWPLAN_TEXT, SHOWPLAN_ALL, and SHOWPLAN_XML, which generate estimated execution plan output, none of the Transact-SQL statements in the batch are executed, except the USE database_name statements, which are executed for these Showplan SET options.

SHOWPLAN Permission Check Examples

The context database is set in batches by using the USE database_name statement. The databases that contain objects, such as tables or views, that are referred to in Transact-SQL statements are checked for the SHOWPLAN permission. However, the SHOWPLAN permission is not checked on context databases unless the Transact-SQL statement refers to objects in the context database.

If a batch contains a USE database_name statement, the context database changes. In this case, the SHOWPLAN permission is not checked for the context database that is in effect when a particular statement occurs in a batch.

For example, in the following batch the SHOWPLAN permission is checked on the AdventureWorks2008R2 database for both of the SELECT statements. The SHOWPLAN permission is not checked on either the master or the tempdb databases, which are referred to in USE statements:

SET SHOWPLAN_XML ON
GO
USE tempdb
SELECT * FROM AdventureWorks2008R2.Person.Address
USE master
SELECT * FROM AdventureWorks2008R2.Person.Address
GO

SHOWPLAN Permission Checks for Dynamic SQL

  • For dynamic SQL statements of the form EXEC (command_string) or EXEC (character_string), no SHOWPLAN permissions checks are performed for the statement, including the embedded string.

  • For dynamic SQL statements that execute a stored procedure of the form EXEC dbo.my_stored_procedure, no SHOWPLAN permission is checked for the EXEC statement itself. However, because a Showplan is produced for the entire stored procedure body, databases that contain objects referred to by statements in the stored procedure are checked for the SHOWPLAN permission.

The following example shows how SHOWPLAN permissions are checked for dynamic SQL statements. For the SELECT statement, the SHOWPLAN permission is checked on the AdventureWorks2008R2 database. No SHOWPLAN permission checks are performed on the EXEC statement itself, tempdb database, or on the master database:

SET SHOWPLAN_XML ON
GO
USE tempdb
EXEC ('USE master; SELECT * FROM AdventureWorks2008R2.Person.Address');

In the following example, where SET STATISTICS XML is used, if the batch is sent to the master database, no SHOWPLAN permission is checked on the SET statement. No SHOWPLAN permission is checked on the USE tempdb statement. However, because the CONNECT permission is checked for the USE statement, the user must already exist in the tempdb database and have the CONNECT permission on the tempdb database. No SHOWPLAN permission is checked for the EXEC statement because no Showplan output is generated by it. No SHOWPLAN permission is checked on the USE master statement. However, the SHOWPLAN permission is checked on the AdventureWorks2008R2 database for the SELECT statement:

SET STATISTICS XML ON
GO
USE tempdb
EXEC ('USE master; SELECT * FROM AdventureWorks2008R2.Person.Address');

Note

The SHOWPLAN permission is not checked for queries that reference previous versions of SQL Server. However, for parts of such queries that run on SQL Server 2005 and later, the SHOWPLAN permission is checked.

Batches Containing Errors

If one of the Showplan SET statement options is used with a Transact-SQL batch that produces either a statement execution error or a permission check error, the context database is determined as follows depending on which SET option mode is used:

  • SHOWPLAN_TEXT, SHOWPLAN_ALL, and SHOWPLAN_XML

    The context database that was in effect before the batch execution started remains in effect. No Showplan output is generated for the entire batch if any statement execution or permission check errors are encountered. All permission checks and statements must execute successfully, or no Showplan output is produced.

  • STATISTICS PROFILE and STATISTICS XML

    The context database that was in effect when the last statement executed successfully remains in effect. Showplan output is generated for each statement in the batch where the statement executed successfully and the permission checks succeeded. Showplan output is produced for each statement in the batch that executes successfully and for which permission checks succeed.

See Also

Concepts

Showplan Security