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.
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),
39 Comments. Leave new
CREATE DATABASE permission denied in database ‘master’
How to create SHOWPLAN role, i had use above query but getting message like in-correct syntex
Now working fine using belwo query
USE DBname
GO
CREATE ROLE [db_showplan] AUTHORIZATION [dbo]
GO
GRANT SHOWPLAN to [db_showplan]
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
Have you tried “run as administrator” option under right click? Do you have permissions?
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.
I got the below error message. Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
‘NT AUTHORITYANONYMOUS LOGON’ is generally sent when there is a problem with SPN. You can download and use http://www.microsoft.com/en-us/download/details.aspx?id=39046 to find / fix SPN issue.
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
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
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