asked on

Execution plan

Hi,

I am trying to execute a dmv query with login credentials without sysadmin rights.
I have given GRANT VIEW SERVER STATE TO [username] rights to this user

Below is the query trying to execute.

SELECT TOP 100 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan,
sp.hostname,
sp.program_name,
sp.nt_username,
sp.loginame
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.sysprocesses sp
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC .

I am able to execute the dmv query , however the execution plan is not
visible in the graphical format but can only be seen in xml form.
Kindly advise how this can be achieved.

Thanks,

Anthony Perkins

From SQL Server's BOL:

To use the graphical execution plan feature in Management Studio, and to use the Showplan Transact-SQL SET statement options, users must have sufficient permissions to execute the Transact-SQL statements and queries. Users must also be granted the SHOWPLAN permission for all databases containing referenced objects.

Also read the following article also from SQL Server's BOL:
Showplan Security
http://msdn.microsoft.com/en-us/library/ms189602(v=sql.105).aspx

SP_2018 .

ASKER

Hi,

Thanks for your reply.

User has SHOWPLAN permissions for all databases but has not been given sysadmin rights
however when the above query is run with the user's login credentials, the execution plan is not visible in the graphical format but can only be seen in xml form.

When i run the same query with my login credentials which has sysadmin rights
i am able to view the execution plan in the graphical format.

Following permission have been granted to the user:

GRANT VIEW SERVER STATE TO [username] rights to this user(for viewing DMV queries)
GRANT SHOWPLAN to [username] (to view the execution plan)

Are their any specific permission which need to be given to the user to view
execution plan in graphical format and not in xml form .

Thanks.

Anthony Perkins

Which execution plan are you talking about: Estimated or Actual?

SP_2018 .

ASKER

Its Actual execution plan

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Did you resolve this problem and if so what was the solution?