Execution plan

Posted on 2012-08-23
Last Modified: 2012-08-26

 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
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
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.

Question by:isonali
    LVL 75

    Expert Comment

    by: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

    Author Comment


    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 .

    LVL 75

    Expert Comment

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

    Author Comment

    Its Actual execution plan
    LVL 75

    Accepted Solution

    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.
    LVL 75

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API ( has made its way into the popular lexicon of the English language.  A few years ago, …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now