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.
SQL Server Portal 