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

    Like

  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

    Like

  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

    Like

  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

    Like

  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.

    Like

  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.

    Like

  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)

    Like

  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

    Like

  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
    ——————————

    Like

  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
    ——————————

    Like

  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.

    Like

  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?

    Like

  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

    Like

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