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.

Solarwinds

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

Solarwinds
, , , ,
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

  • manishsiddhapara
    April 25, 2013 11:48 am

    CREATE DATABASE permission denied in database ‘master’

    Reply
  • How to create SHOWPLAN role, i had use above query but getting message like in-correct syntex

    Reply
  • Now working fine using belwo query
    USE DBname
    GO
    CREATE ROLE [db_showplan] AUTHORIZATION [dbo]
    GO

    GRANT SHOWPLAN to [db_showplan]

    Reply
  • Hi,
    Urgent!!!
    when opening my application that queries a database sql server 2012 r2 under version windows 2012 R2, I face this message

    CREATE DATABASE permission denied in database ‘master’

    thank you for your help

    Reply
    • Have you tried “run as administrator” option under right click? Do you have permissions?

      Reply
  • Hello,
    I am able to run my query when I open SQL Server Management Studio –> ‘run as administrator’

    However, I am not able to run the same query from my C# program.
    cn = new SqlConnection(@”Data Source=serverName;Initial Catalog=DBname;Integrated Security=True”);

    Pls assist. Thank you.

    Reply
  • I got the below error message. Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

    Reply
  • Hello All,
    I have provided the Showplan permission. I thought of to confirm whether the permission has been applied by running a script.

    But its not listing the provided permissions in that particular db.

    So, how should I confirm whether the permission has applied or not

    Reply
  • the script that I used as below
    select sys.schemas.name ‘Schema’
    , sys.objects.name Object
    , sys.database_principals.name username
    , sys.database_permissions.type permissions_type
    , sys.database_permissions.permission_name
    , sys.database_permissions.state permission_state
    , sys.database_permissions.state_desc
    , state_desc + ‘ ‘ + permission_name + ‘ on [‘+ sys.schemas.name + ‘].[‘ + sys.objects.name + ‘] to [‘ + sys.database_principals.name + ‘]’ COLLATE LATIN1_General_CI_AS
    from sys.database_permissions
    join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
    join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
    join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
    WHERE sys.database_principals.name = ‘your_user_or_role’
    order by 1, 2, 3, 5

    Reply
  • i have installed sql server 2014 but i face the problem in creating new database . this error msg show Msg 262, Level 14, State 1, Line 2
    CREATE DATABASE permission denied in database ‘master’.

    pls give me solution if you have.

    need for help

    thanks

    Reply

Leave a Reply

Menu