I came across a curious issue the other day when trying to look at the actual execution plan of a stored procedure that was being developed. Strangely I couldn’t get an estimated plan either. All I got was the confusing error:
SHOWPLAN permission denied in database ‘TestDB’
As a sysadmin on the system in question I was very confused by this and it took a little bit of investigation to find out that the problem was actually being caused by the execution context of the stored procedure.
Here’s a quick repro:
First, create a new user and table, then load that table up with a few rows (just for demo purposes)
IF (SELECT name FROM sys.databaseprincipals WHERE name = 'TestUser') IS NULL
CREATE USER [TestUser] WITHOUT LOGIN;
GO
IF (SELECT OBJECTID('dbo.SomeData', N'T')) IS NULL
BEGIN
CREATE TABLE dbo.SomeData
(
Id INT IDENTITY(1,1) NOT NULL
, NothingNess CHAR(10) DEFAULT ''
, CONSTRAINT PK_SomeData PRIMARY KEY(Id)
)
END
GO
INSERT INTO dbo.SomeData DEFAULT VALUES;
GO 100
Now we’ll create a couple of procs to pull data from the table. The first will execute under the context of the calling user, and the other under the context of the user TestUser that was just created.
CREATE PROCEDURE dbo.GetSomeData @Id INT
AS
SELECT Id, NothingNess
FROM dbo.SomeData
WHERE Id = @Id
GO
CREATE PROCEDURE dbo.GetSomeDataTestUser @Id INT
WITH EXECUTE AS 'TestUser'
AS
SELECT Id, NothingNess
FROM dbo.SomeData
WHERE Id = @Id
GO
Now we can execute both of these procedures and get results just fine.
EXEC dbo.GetSomeData 1;
EXEC dbo.GetSomeDataTestUser 1;
However if we attempt to run while grabbing the actual execution plan, or attempt to grab an estimated plan then the second call dbo.GetSomeDataTestUser will fail with the SHOWPLAN permission denied error.
What happens is that when the stored procedure runs it does so under the context of TestUser, and that user does not have SHOWPLAN permissions in the database. This means that you cannot display an execution plan, not could you gather TIME or IO statistics.
This didn’t make much sense to me at first, just because I was a sysadmin, but after thinking for a while it is actually the correct way to handle things. All the permissions are contained for execution of the code within TestUser. So if TestUser does not have the rights to do something, or access something then the calling user would not be able to get to that information.
As a workaround for this all that needs to happen is to grant the showplan permission to TestUser and everything will be fine. For example:
GRANT SHOWPLAN TO TestUser;
It’s worth noting that this permission is database scoped, so don’t expect to execute it in one database and have it work everywhere.
Technet has a good write-up about all the things covered by SHOWPLAN, it is worth a read for more information.
TL;DR – If you get SHOWPLAN denied errors check that the context for the user actually execution the query has the requisite permissions, and grant showplan if needed.
SirSQL.net 