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.
Here is the long version of the reproduce step.
First, lets create the store procedure
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.
Lets login as bob and try to execute the store procedure.
Result looks ok
Lets try the set up the show plan
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
Here is the Query plan
Lets clean up the environment