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