Showplan Error

So there I am, eating some meat pie for lunch, thinking about how much better it would be if it had some bacon, when I perform a very common task that results in a very uncommon error. Well, uncommon for me at least.

I went to display the estimated execution plan for a stored procedure and was greeted with this message (i have altered the procedure and database names):

Huh? I cannot generate a showplan? How is that possible? As a sysadmin I am not used to seeing error messages when I perform routine tasks, so I was quite surprised by this one. As luck would have it, the error message does not quite tell the entire story, because it gives the impression that I do not have permissions, which is not the case. Turns out the procedure is using the EXECUTE AS clause and the user executing the procedure does not have the necessary permissions.

So, here is a quick test script that you can run to see the issue for yourself. First, create a login to the server or as I have done a user in the database without a specified login. I even set a default schema but you can leave that option off and it will default to the dbo schema.

Next, create a stored procedure and use the EXECUTE AS clause as follows:

CREATE PROCEDURE usp_showplan_error
WITH
EXECUTE AS ‘USR_NOLOGIN’
AS
BEGIN
SELECT * FROM SYSUSERS
END

Next, double-click on the name of the procedure, then do a right-click and choose ‘Display Estimated Execution Plan’. You should see the same error I witnessed. The workaround? It is simple:

GRANT SHOWPLAN TO [USR_NOLOGIN]

The lesson? First, bacon is to food what black is to fashion: it goes with everything. Second, error messages have a habit of not being helpful and at the same time being quite accurate. Good luck determining that upon first glance. Lastly, SQL 2005 comes with a decent level of granularity as far as permissions go, and it is worthwhile to understand that nuances like this can happen.