So there I am, eating some meat pie for lunch, thinking about how much better it would be if it had some bacon, when I perform a very common task that results in a very uncommon error. Well, uncommon for me at least.
I went to display the estimated execution plan for a stored procedure and was greeted with this message (i have altered the procedure and database names):
Msg 262, Level 14, State 4, Procedure <proc_name>, Line 1
SHOWPLAN permission denied in database ‘<dbname>’.
Huh? I cannot generate a showplan? How is that possible? As a sysadmin I am not used to seeing error messages when I perform routine tasks, so I was quite surprised by this one. As luck would have it, the error message does not quite tell the entire story, because it gives the impression that I do not have permissions, which is not the case. Turns out the procedure is using the EXECUTE AS clause and the user executing the procedure does not have the necessary permissions.
So, here is a quick test script that you can run to see the issue for yourself. First, create a login to the server or as I have done a user in the database without a specified login. I even set a default schema but you can leave that option off and it will default to the dbo schema.
CREATE USER [USR_NOLOGIN] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[USR_NOLOGIN]
Next, create a stored procedure and use the EXECUTE AS clause as follows:
CREATE PROCEDURE usp_showplan_error
WITH
EXECUTE AS ‘USR_NOLOGIN’
AS
BEGIN
SELECT * FROM SYSUSERS
END
Next, double-click on the name of the procedure, then do a right-click and choose ‘Display Estimated Execution Plan’. You should see the same error I witnessed. The workaround? It is simple:
GRANT SHOWPLAN TO [USR_NOLOGIN]
The lesson? First, bacon is to food what black is to fashion: it goes with everything. Second, error messages have a habit of not being helpful and at the same time being quite accurate. Good luck determining that upon first glance. Lastly, SQL 2005 comes with a decent level of granularity as far as permissions go, and it is worthwhile to understand that nuances like this can happen.
July 30, 2015 by Muhammad Imran
Few days ago, I created some users in my development environment and gave them access to use my development database. After sometime, I received a complaint that when they run a normal query it is working fine but when they try to run a query along with execution plan (Ctrl + M), SQL server generates given below error.
Let me explain this error in detail :
Message Number: 262
Severity : 14
Error Message: SHOWPLAN permission denied in database ‘AdventureWorks2012’.
Error Generation:
Let me create a sample to demonstrate this error.
USE AdventureWorks2012 GO --Create a user CREATE USER Imran WITHOUT LOGIN; GO --Grant select permission to Imran user GRANT SELECT ON [HumanResources].[Department] TO Imran; GO -- Press Ctl+M to Include Actual Execution Plan EXECUTE AS USER = 'Imran'; SELECT * FROM [HumanResources].[Department] ; REVERT;
Msg 262, Level 14, State 4, Line 12
SHOWPLAN permission denied in database ‘AdventureWorks2012’.
Ooopps…… I am unable to show the actual execution plan (Ctrl + M) of the select query as shown above.
Resolution:
The resolution is very simple, actually, once you created the user you did not grant SHOWPLAN access to the USER (Imran). Lets give this user the SHOWPLAN access. Given below is the script.
Grant SHOWPLAN access
USE AdventureWorks2012 GO GRANT SHOWPLAN TO Imran GO -OUTPUT
Command(s) completed successfully.
Now, the SHOWPLAN access has been granted, lets re-run the above query with execution plan and it works as shown below.
Conclusion :
Remember, whenever you want any user to include the execution plan in the query, you MUST give him SHOWPLAN access.
Posted in Errors, SQL SERVER | Tagged raresql, SHOWPLAN permission denied in database, SQL, SQL Server | 1 Comment
Do you get the error «SHOWPLAN permission denied in database ‘DBName'»? This error is due to SQL Server permissions. Find out how to fix SQL Server permissions and remove the error. Read more here.
Do you want a query execution plan in SQL Server for performance tuning and getting the following error?
Msg 262, Level 14, State 4, Line 2
SHOWPLAN permission denied in database ‘DBName’.
As per the error message, you do not have permission to view the execution plans. To resolve this error, run the following T-SQL script from the admin user or SA account.
USE DBName
GO
GRANT SHOWPLAN TO UserName
GO
Once you execute the GRANT SHOWPLAN, you can view the estimated and actual execution plan in SQL Server.
Проблема
Ошибка СУБД: Microsoft OLE DB Provider for SQL Server: SHOWPLAN permission denied in database ‘tempdb’.
HRESULT=80040E14, SQLSrvr: SQLSTATE=42000, state=4, Severity=E, native=262, line=1
Решение
Сделайте учетную запись службы сервера 1С:Предприятие с полными правами на tempdb
кто то скажет «о, небезопасно» — ну хакните, попробуйте 
Обсудить детали можно в форуме.
Recently I got a interesting issue where I was getting error ‘SHOWPLAN permission denied in database ‘SSISDB” even though I was having SYSADMIN permissions on the SQL instance. This is in continuation of previous article https://wordsontech.com/failed-to-execute-is-server-package-because-of-error-0x80131904/
I was trying to get estimated exeution plan for the stored procedure “internal.cleanup_server_retention_window” using the show estimated execution plan button in SSMS, I encountered below error:
Msg 262, Level 14, State 4, Procedure cleanup_server_retention_window, Line 169 [Batch Start Line 0]
SHOWPLAN permission denied in database 'SSISDB'.
It was strange for me as I was having SYSADMIN rights on the SQL instance and was not expecting this error. When I checked the text of the stored procedure, I caught the reason behind the error. As per below lines of stored procedure, it was being executed as user ‘AllSchemaOwner’ which was not having required permissions to view the execution plan.
When I commented out above lines along with last line “RETURN 0” and fetched the estimated execution plan, I got the execution plan without error.
In summary, if we encounter permission error while fetching execution plan of a query/stored procedure while having sysadmin rights, we must check if that query/stored procedure is being executed as another user and if that user is having required permissions to view the execution plan.
We can mitigate this error either by omitting ‘execute as’ caluse or by giving showplan permissions to that user by below command.
USE <DBNAME>
GO
GRANT SHOWPLAN TO <User>
GO
Hope this article will help you !
Happy Learning!!
Post Views: 19