2. srpna 2012 13:19
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.
2. srpna 2012 13:29Přispěvatel
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?
Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/
- Označen jako odpověď Shiju Samuel 2. srpna 2012 15:35
2. srpna 2012 15:36Thanks Uri!