?
Solved

Execution plan

Posted on 2012-08-23
6
Medium Priority
?
507 Views
Last Modified: 2012-08-26
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,
0
Comment
Question by:Sonali P
  • 4
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38327784
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
0
 

Author Comment

by:Sonali P
ID: 38330221
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38330528
Which execution plan are you talking about: Estimated or Actual?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:Sonali P
ID: 38331436
Its Actual execution plan
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 38331570
If you have done the following (again from SQL Server BOL):
Appropriate permissions to execute the Transact-SQL statements.
SHOWPLAN permission on all databases containing objects that are referenced by the Transact-SQL statements.
And they still cannot view the graphical executional plan, than I have no idea.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38334531
Did you resolve this problem and if so what was the solution?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question