SQL SERVER – Fix: Error: 262 : SHOWPLAN permission denied in database

During one of my recent training class when I asked students to check the execution plan using (can be enabled using CTRL+M), they received error as follows.

SQL SERVER - Fix: Error: 262 : SHOWPLAN permission denied in database execution-plan Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database ‘AdventureWorks’.

This is quite common now that all the developers have admin rights. It was very easy to fix this issue.

Fix Workaround:

USE AdventureWorks
GO
GRANT SHOWPLAN TO UserName
GO

Just run above statement in the database where the user is getting an error and he will be able to see the query execution plan without any issue.

It is very critical to see the plan for any query as with the help of the query plan, we can figure out what is happening under the hood for any query. An estimated execution plan is a SQL Server query plan that is generated without actually running the query plan is created for. It is useful for analyzing how a query would behave, without actually running it. The graphical execution plan a tool that presents in images and text, the process by which SQL Server steps through the query.

Let me know what you think of this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com),

, , , ,
Previous Post
SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON
Next Post
SQL SERVER – SQL Server RDL Specification

Related Posts

39 Comments. Leave new

  • i am getting this error when i tried to create database in sql Msg 262, Level 14, State 1, Line 1
    CREATE DATABASE permission denied in database ‘tempdb’.
    how to remove this error
    thanx in advance
    saumya

    Reply
    • that gave me my error but that the operating system is
      Windows Vista or Windows 7, to fix it:
      1.When the full install by the sql, click
      sql properties> compatibility> compatibility mode>
      Run this program in compatibility mode for:
      select and ready …

      Reply
  • How can i fix “CREATE DATABASE permission denied in database ‘master'”.
    i am using sql 2008 no sample databases how can i install sample database using it

    Reply
  • probably because you’re working on Windows 7 before starting the application right click on the icon of SQL Server Management Studio> Run as administrator. icon in the SQL Server Management Studio> Properties> Compatibility> Run this program as support for (windows xp service pack 3)> privilege level (the active) and ready.
    All this is in the tab properties> compatibility.

    You can also right click icon in SQL Server Management Studio and then select Run as administrator

    Reply
  • probably because you’re working on Windows 7 before starting the application right click on the icon of SQL Server Management Studio> Run as administrator. icon in the SQL Server Management Studio> Properties> Compatibility> Run this program as support for (windows xp service pack 3)> privilege level (the active) and ready.
    All this is in the tab properties> compatibility.

    You can also right click icon in SQL Server Management Studio and then select Run as administrator

    Reply
  • i cant create database… i have done almost everything what d answers are but dint succeeded..plz give d solution for dis… thanks in advance…
    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Create failed for Database ‘uyiuyiuyi’. (Microsoft.SqlServer.Smo)

    For help, click:

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)

    For help, click:

    ——————————
    BUTTONS:

    OK
    ——————————
    dis is d error i’m getting plz as early as poosiblee.

    Reply
  • @ Freddy

    Thanks a tonne sir!
    Really needed that badly..

    Peace!

    Reply
  • the problem mentioned by the friend Siddhi, is regarding the compatibility of sql server 2005 with windows7 opertativos systems and Vista, to avoid these problems you can upgrade to the version of SQL 2008, or run it on: compatibility mode.

    Reply
  • thanks a ton bro Abhishek :)

    Reply
  • Thanks you so much :)

    Reply
  • in sql server 2008 i had this problem , msg 262 line 14 CREATE DATABASE permission is denied in ‘master ‘
    i connect in mixed mode(sql server and windows authendication mode)

    Reply
  • I got error run I run below script:

    USE Clarity
    GO
    GRANT SHOWPLAN TO ‘Liife\phiraiwa’
    GO
    ****************
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘Liife\phiraiwa’.
    ****************

    tried without single code ‘….’ got diffirent error

    USE Clarity
    GO
    GRANT SHOWPLAN TO Liife\phiraiwa
    GO
    ****************
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘\’.
    ****************
    Please advice,
    Thanks a lot

    Reply
  • Thanks a lot.

    Reply
  • Try this:
    GRANT SHOWPLAN TO [Liife\phiraiwa]

    Reply
  • hi

    Iam also getting same error in windows 7
    probably because you’re working on Windows 7 before starting the application right click on the icon of SQL Server Management Studio> Run as administrator. icon in the SQL Server Management Studio> Properties> Compatibility> Run this program as support for (windows xp service pack 3)> privilege level (the active) and ready.
    All this is in the tab properties> compatibility.

    You can also right click icon in SQL Server Management Studio and then select Run as administrator

    I tried all these but still saying as

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Create failed for Database ‘WT’. (Microsoft.SqlServer.Smo)

    For help, click:

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)

    For help, click:

    ——————————
    BUTTONS:

    OK
    ——————————

    Reply
  • hi

    Iam also getting same error in windows 7
    probably because you’re working on Windows 7 before starting the application right click on the icon of SQL Server Management Studio> Run as administrator. icon in the SQL Server Management Studio> Properties> Compatibility> Run this program as support for (windows xp service pack 3)> privilege level (the active) and ready.
    All this is in the tab properties> compatibility.

    You can also right click icon in SQL Server Management Studio and then select Run as administrator

    I tried all these but still saying as

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Create failed for Database ‘WT’. (Microsoft.SqlServer.Smo)

    For help, click:

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)

    For help, click:

    ——————————
    BUTTONS:

    OK
    ——————————

    Reply
  • I was not able to Create a Database using Mgmt Studio 2008, and was getting similar exception even though I had logged in to the machine as an Administrator. But now it is fixed. The option that I chose is different from all other options mentioned above. After double clicking Mgmt Studio icon I connected to \sqlexpress instead of . And now, I’m able to create and Restore Database. I don’t know this is the correct way or not.

    Reply
  • TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    Create failed for Database ‘slaeh’. (Microsoft.SqlServer.Express.Smo)

    For help, click:

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

    ——————————

    CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)

    For help, click:

    ——————————
    BUTTONS:

    OK
    ——————————
    please help me.what can i do this problem?

    Reply
  • Thanx Avishek :)

    Reply
  • Just want to add info here: Please use double quotes as single quotes are not working. BTW I’m using SQL server 2012
    USE Database
    GO
    GRANT SHOWPLAN TO “User”
    GO

    Reply
  • What will happens if we don’t have people like you!

    Sun

    Reply

Leave a Reply

Menu