VIEW Server State Permission and SHOWPLAN

Question

As I understand Vew server state permission is a server level permission and SHOWPLAN is a database level permission.

If a user has serverstate permission and don't have show plan permission, will he be able to get plan from cache through dm_exec_query_plan DMF. Also I think he will not be able to get the plan from [set showplan_xml on]

Is my understanding correct? I want to understand how these two distinguish as far as viewing plan's are concern.

 

Thanks, Shiju

 

 

Answer 1

BOL says.

To execute sys.dm_exec_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

In order to use SET SHOWPLAN_XML, you must have sufficient permissions to execute the statements on which SET SHOWPLAN_XML is executed, and you must have SHOWPLAN permission for all databases containing referenced objects.

For SELECT, INSERT, UPDATE, DELETE, EXEC stored_procedure, and EXEC user_defined_function statements, to produce a Showplan the user must:

• Have the appropriate permissions to execute the Transact-SQL statements.

• Have 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 SQL, and so on, only the appropriate permissions to execute the Transact-SQL statements are needed.

>>>Is my understanding correct?

Yes.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Thanks Uri!