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 following.

Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database ‘AdventureWorks’.

This is quite common that now 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 user is getting error and he will be able to see the query execution plan without any issue.

Reference: Pinal Dave (http://blog.SQLAuthority.com),

About these ads

30 thoughts on “SQL SERVER – Fix: Error: 262 : SHOWPLAN permission denied in database

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

    ——————————
    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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

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

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

  6. 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.

  7. 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)

  8. 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

  9. 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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

    ——————————
    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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

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

    OK
    ——————————

  10. 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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

    ——————————
    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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

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

    OK
    ——————————

  11. 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.

  12. TITLE: Microsoft SQL Server Management Studio Express
    ——————————

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

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

    ——————————
    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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

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

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

  13. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s