Tags: database, denied, following, grant, ltusergt, message, microsoft, mysql, oracle, permission, query, received, server, showplan, sql, statistics, user

Grant Showplan to <user>

On Database » Microsoft SQL Server

2,921 words with 5 Comments; publish: Thu, 20 Dec 2007 20:50:00 GMT; (250297.00, « »)

I was trying to review some query statistics and received the following message:

SHOWPLAN permission denied in database Test

I gave the user permission by the following command:

Grant showplan to user.

I am curious as to how much perfomance does this effect? Is there an alternative?

regards

All Comments

Leave a comment...

  • 5 Comments
    • The SHOWPLAN permission only governs who can run the various SET SHOWPLAN statements. It doesn't impact performance of the server per se. And with some of the SHOWPLAN statement in effect, the statement(s) is not executed and goes through compilation phase only. There is no other alternative though other than granting the required permission to the user.
      #1; Mon, 10 Sep 2007 16:24:00 GMT
    • Does it have anything to do with "Display estimated execution plan" or "Include actual execution plan" in query analyzer?

      Thanks

      #2; Mon, 10 Sep 2007 16:25:00 GMT
    • Yes. Those features run one of the SET SHOWPLAN command underneath the covers. See the BOL link below for more details on the permissions:
      ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9c18657e-2992-4ee7-ab07-7af3141ec658.htm
      The online version of BOL links is:
      #3; Mon, 10 Sep 2007 16:27:00 GMT
    • Thanks for your prompt reply.

      Just one last question.

      If i have a query that takes about 60 minutes to run and i have the "Show actual estimation plan" selected...that would not effect the server perrformance or clog it up as compare to if i just run the query?

      Thanks

      #4; Mon, 10 Sep 2007 16:27:00 GMT
    • The actual estimation plan uses "SET STATISTICS XML ON" in SQL Server 2005 and "SET STATISTICS PROFILE ON" in older versions of SQL Server. It will not affect the query performance drastically. You may want to just use SET STATISTICS TIME ON first to see the compilation and execution times. That will give an idea as to where the chunk of query execution time is being spent. You can then use the actual plan output to look for missing indexes, scans etc.
      #5; Mon, 10 Sep 2007 16:28:00 GMT