January 21, 2009 at 6:20 pm
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.
January 24, 2009 at 7:51 pm
Show plan is based on statistics in existing tables. It does not work on any temporary table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply