SHOWPLAN permission denied in database 'tempdb'

Question

Post reply

SHOWPLAN permission denied in database 'tempdb'

Aparna-1

SSCrazy

Points: 2339
More actions
January 22, 2009 at 9:27 pm

#200330

I have already posted this in SQL Server Newbies forum I posting this once again in this forum. Please help........
I am testing some code where I am creating two temp tables and a non clustered index one one of them, when I run the query and select Display Graphical SHowplan it comes up with the execution plan but when I execute as a user it gives me the following error SHOWPLAN permission denied in database 'tempdb'
Attached is a sample code
EXEC AS USER = 'aparnak' --Check execution plan
CREATE TABLE #Dept
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name VARCHAR(255) NOT NULL
)
INSERT INTO #Dept
(Name)
VALUEs
('IT')
INSERT INTO #Dept
(Name)
VALUEs
('Analysis')
--SELECT * FROM #Dept
CREATE TABLE #Employee
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DeptID INT NOT NULL,
Name VARCHAR(255)
)
INSERT INTO #Employee
(
DeptID,
Name
)
VALUES
(
1,
'aparna'
)
INSERT INTO #Employee
(
DeptID,
Name
)
VALUES
(
1,
'Sabrina'
)
CREATE NONCLUSTERED INDEX IX_Employee_DeptID ON #Employee (DeptID ASC) INCLUDE (Name)
SELECT #Employee.Name, #Dept.Name FROM #Employee
JOIN #Dept
ON #Dept.ID = #Employee.DeptID
DROP TABLE #Dept
DROP TABLE #Employee
REVERT
Note I have also created the same user aparnak in tempdb and granted the SHOWPLAN option to the user.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 1

It helps to know if this is occuring in SQL 2K5 or SQL 2K8?

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I'm pretty sure you need at least DBO privs to do a SHOWPLAN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aparna-1 SSCrazy Points: 2339 More actions · Answer 3

It is occuring in SQ2005 and I am logging in as sa with all privileges.

Before the exec as user = 'aparnak' I am using the following statement GRANT SHOWPLAN TO aparnak and after the REVERT statement I am using REVOKE SHOWPLAN FROM aparnak.

I have this problem only while executing this from a test database (say database XYZ), when I use the same code in tempdb database it works fine and I can see the execution plan

Aparna-1 SSCrazy Points: 2339 More actions · Answer 4

probably the problem i stated is not very clear, the user has SHOWPLAN permission just that when an index is created and while I execute as a user it throws the following error, I have another sample code from AdventureWorks database

USE AdventureWorks

GO

--EXEC AS USER = 'dbo'

CREATE TABLE #Employee

(

EmployeeID INT NOT NULL,

PersonID INT NOT NULL,

HireDate DATETIME NOT NULL

)

INSERT INTO #Employee

SELECT TOP 50

EmployeeID,

ContactID,

HireDate

FROM

HumanResources.Employee

CREATE NONCLUSTERED INDEX IX_Employee_PersonID ON #Employee(PersonID) INCLUDE(EmployeeID)

SELECT

E.EmployeeID,

Contact.FirstName,

Contact.LastName

FROM

#Employee E

JOIN

Person.Contact

ON

Contact.ContactID = E.PersonID

DROP TABLE #Employee

--REVERT

Now uncomment the exec as user it throws the same error. I am not sure where I am going wrong. Or comment craete index statement and exec as user it works fine.

john.delnostro SSC Rookie Points: 45 More actions · Answer 5

Run the following...

USE tempdb

GRANT SHOWPLAN To [User]

Try this.