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.
- GRANT SHOWPLAN to USERNAME
Here is the long version of the reproduce step.
First, lets create the store procedure
- use AdventureWorks;
- CREATE PROCEDURE Production.GetProducts @Name NVARCHAR(50)=N'%'
- AS
- SELECT ProductID, [Name], ListPrice
- FROM Production.Product
- WHERE [Name] LIKE @Name
- ORDER BY ProductID
- 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.
- USE [master]
- GO
- CREATE LOGIN [bob] WITH PASSWORD=N'12345',
- DEFAULT_DATABASE=[AdventureWorks]
- USE AdventureWorks
- CREATE USER [bob] For LOGIN [bob]
- GRANT EXECUTE ON Production.GetProducts TO [bob]
Lets login as bob and try to execute the store procedure.
- exec Production.GetProducts;
Result looks ok
Lets try the set up the show plan
- 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
- GRANT SHOWPLAN to [bob]
- SET SHOWPLAN_XML ON
- exec Production.GetProducts;
Here is the Query plan
Lets clean up the environment
- USE AdventureWorks;
- DROP USER [bob];
- DROP PROCEDURE Production.GetProducts;
- USE master;
- DROP LOGIN [bob];
Thanks for the always useful information. This is great information to help garage type SEO people like me.RazorSQL 7.3.2
ReplyDeleteAt Rolex, we believe Replica Rolex Watchesin maintaining long-standing commitments with exceptional Replica Oyster Perpetual Submariner individuals, organizations, events and institutions in domains as varied as exploration, sports, arts & culture, science, and education.
ReplyDelete