2 How to fix the 262 show plan permission deny error

One of common technical to trouble shooting the permission issue is looking up the query plan.  As a system DBA, I may want to give my developer permission to check the query plan instead of doing every thing for them. If they do not possess proper permission, they may get the 262 error.

The simple grant statement can fix the issue.

1. GRANT SHOWPLAN to USERNAME

Here is the long version of the reproduce step.

First, lets create the store procedure

1. use AdventureWorks;
2. CREATE PROCEDURE Production.GetProducts @Name NVARCHAR(50)=N'%'
3. AS
4. SELECT ProductID, [Name], ListPrice
5. FROM Production.Product
6. WHERE [Name] LIKE @Name
7. ORDER BY ProductID
8. GO

Second, lets create the new login call bob and add bob as user in the AdventureWorsks, then grant bob exec permission on our new sp.

1. USE [master]
2. GO
3. CREATE LOGIN [bob] WITH PASSWORD=N'12345',
4. DEFAULT_DATABASE=[AdventureWorks]
5.  
6. USE AdventureWorks
7.  
8. CREATE USER [bob] For LOGIN [bob]
9.  
10. GRANT EXECUTE ON Production.GetProducts TO [bob]

Lets login as bob and try to execute the store procedure.

1. exec Production.GetProducts;

Result looks ok

Lets try the set up the show plan

1. SET SHOWPLAN_ALL ON

Bingo !! You get the 262 error

Same permission is apply to any of below set commands

• SET SHOWPLAN_ALL ON
• SET SHOWPLAN_XML ON
• SET SHOWPLAN_TEXT ON
• SET STATISTICS XML ON
• SET STATISTICS PROFILE ON

However, below 2 set option does not require the showplan permssion

• SET STATISTICS TIME ON
• SET STATISTICS IO ON

Once we grant the bob showplan permission

1. GRANT SHOWPLAN to [bob]

 

1. SET SHOWPLAN_XML ON
2. exec Production.GetProducts;

Here is the Query plan

Lets clean up the environment

1. USE AdventureWorks;
2. DROP USER [bob];
3. DROP PROCEDURE Production.GetProducts;
4. USE master;
5. DROP LOGIN [bob];