Avatar of enrique_aeo
enrique_aeo
 asked on

SHOWPLAN permission denied in database 'AdventureWorks'.

i "use Windows autentication"

i execute this t-sql
use AdventureWorks
go
grant showplan to [SMI\elopezh]

the message is: Command(s) completed successfully.

i "included actual execution plan"
but i have this error
Msg 262, Level 14, State 4, Line 3
SHOWPLAN permission denied in database 'AdventureWorks'.

the t-sql code this
DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC [dbo].[GetCustomerInformation] @CustomerID = @CustID, @LastName = @Lname'
DECLARE @ParmDefinition NVARCHAR (500) = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'
DECLARE @CustomerID BIGINT = 1
DECLARE @LastName VARCHAR(30) = 'Achong'

 EXEC sp_executesql @spexecutesqlStr,
                              @ParmDefinition,
                              @CustID = @CustomerID,
                              @LName = @LastName,
                              @FName = null,
                              @MI = null,
                              @Email =null,
                              @RegionNo = null,
                              @CustomerCode= null
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
lcohan

Please have a look here how to fix exactly the same issue you're having in AdventureWorks database:

http://blog.sqlauthority.com/2010/01/05/sql-server-fix-error-262-showplan-permission-denied-in-database/

Obviously that "UserName" must be replaced with the windows login name you're using.
Pawan Kumar

Use below, In your case username is incorrect.

to get the username use - SELECT SUSER_NAME()

--

USE AdventureWorks
GO
GRANT SHOWPLAN TO UserName
GO


--Enjoy

Open in new window


--
enrique_aeo

ASKER
i execute

1.
SELECT SUSER_NAME()

the resulkt is: SMI\elopezh

then
2.
USE AdventureWorks
GO
GRANT SHOWPLAN TO [SMI\elopezh]
GO
this is the results
Command(s) completed successfully.

3.
DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC [dbo].[GetCustomerInformation] @CustomerID = @CustID, @LastName = @Lname'
DECLARE @ParmDefinition NVARCHAR (500) = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'
DECLARE @CustomerID BIGINT = 1
DECLARE @LastName VARCHAR(30) = 'Achong'

 EXEC sp_executesql @spexecutesqlStr,
                              @ParmDefinition,
                              @CustID = @CustomerID,
                              @LName = @LastName,
                              @FName = null,
                              @MI = null,
                              @Email =null,
                              @RegionNo = null,
                              @CustomerCode= null

this is the results
Msg 262, Level 14, State 4, Line 11
SHOWPLAN permission denied in database 'AdventureWorks'.
01.png
02.png
03.png
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Vitor Montalvão

If the user as a DENY permission on SHOWPLAN it will have higher priority than GRANT so before you GRANT the necessary permission you must REVOKE the DENY first:
USE AdventureWorks
GO
REVOKE SHOWPLAN TO [SMI\elopezh]
GO
GRANT SHOWPLAN TO [SMI\elopezh]

Open in new window

enrique_aeo

ASKER
i execute this
USE AdventureWorks
GO
REVOKE SHOWPLAN TO [SMI\elopezh]
GO
GRANT SHOWPLAN TO [SMI\elopezh]

this results:
Command(s) completed successfully.


then, i execute this
DECLARE @spexecutesqlStr NVARCHAR(1000) = 'EXEC [dbo].[GetCustomerInformation] @CustomerID = @CustID, @LastName = @Lname'
 DECLARE @ParmDefinition NVARCHAR (500) = N'@CustID bigint, @LName varchar(30), @FName varchar(30), @MI char(1), @Email varchar(128), @RegionNo tinyint, @CustomerCode tinyint'
 DECLARE @CustomerID BIGINT = 1
 DECLARE @LastName VARCHAR(30) = 'Achong'

  EXEC sp_executesql @spexecutesqlStr,
                               @ParmDefinition,
                               @CustID = @CustomerID,
                               @LName = @LastName,
                               @FName = null,
                               @MI = null,
                               @Email =null,
                               @RegionNo = null,
                               @CustomerCode= null

this is the results
Msg 262, Level 14, State 4, Line 3
SHOWPLAN permission denied in database 'AdventureWorks'.
enrique_aeo

ASKER
Experts, I've tried all solutions but does not work, we can do?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
enrique_aeo

ASKER
It was not solved
ASKER CERTIFIED SOLUTION
Daniel_PL

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
enrique_aeo

ASKER
i execute this
USE AdventureWorks;  
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks.dbo.GetCustomerInformation'));

this is the results:
-- To reduce the potential for SQL injection, use loginless
-- users. Check out the "Little Bobby Tables" blog post for
-- more information on what I'm doing in this code / execution
-- to reduce the potential surface area of the DSE here.
 
--CREATE USER [User_GetCustomerInformation]
--WITHOUT LOGIN;
--GO
 
--GRANT SELECT ON [dbo].[Customers]
--TO [User_GetCustomerInformation];
--GO
 
---- You'll need this if you want to review the showplan
---- for these executions.
--GRANT SHOWPLAN TO [User_GetCustomerInformation];
--GO
 
----------------------------------------------
-- Solution 3
----------------------------------------------
 
CREATE PROC [dbo].[GetCustomerInformation]
(
 @CustomerID BIGINT = NULL
 , @LastName VARCHAR (30) = NULL
 , @FirstName VARCHAR (30) = NULL
 , @MiddleInitial CHAR(1) = NULL
 , @EmailAddress VARCHAR(128) = NULL
 , @Region_no TINYINT = NULL, @Cust_code TINYINT = NULL
)
WITH EXECUTE AS N'User_GetCustomerInformation'
AS
IF (@CustomerID IS NULL
 AND @LastName IS NULL
 AND @FirstName IS NULL
 AND @MiddleInitial IS NULL
 AND @EmailAddress IS NULL
 AND @Region_no IS NULL
 AND @Cust_code IS NULL)
BEGIN
 RAISERROR ('You must supply at least one parameter.', 16, -1);
 RETURN;
END;
 
DECLARE @spexecutesqlStr NVARCHAR (4000),
 @ExecStr NVARCHAR (4000),
 @Recompile BIT = 1;
 
SELECT @spexecutesqlStr =
 N'SELECT * FROM [dbo].[Customers] AS [C] WHERE 1=1';
 
SELECT @ExecStr =
 N'SELECT * FROM [dbo].[Customers] AS [C] WHERE 1=1';
 
IF @CustomerID IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[ContactID] = @CustID';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[ContactID] = CONVERT(BIGINT, ' + CONVERT(NVARCHAR(30),@CustomerID) + N')';
END
 
IF @LastName IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[LastName] LIKE @LName';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[LastName] LIKE CONVERT(VARCHAR(30),' + QUOTENAME(@LastName, '''''') + N')';
END
 
IF @FirstName IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[Firstname] LIKE @FName';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[FirstName] LIKE CONVERT(VARCHAR(30),' + QUOTENAME(@FirstName, '''''') + N')';
END
 
IF @MiddleInitial IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[MiddleInitial] = @MI';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[MiddleInitial] = CONVERT(CHAR(1), ' + QUOTENAME(@MiddleInitial, '''''') + N')';
END
 
IF @EmailAddress IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[EmailAddress] LIKE @Email';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[EmailAddress] LIKE CONVERT(VARCHAR(128), ' + QUOTENAME(@EmailAddress, '''''') + N')';
END
 
IF @Region_no IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[Region_no] = @RegionNo';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[Region_no] = CONVERT(TINYINT, ' + CONVERT(NVARCHAR(5),@Region_no) + N')';
END
 
IF @Cust_code IS NOT NULL
BEGIN
 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[Cust_code] = @MemberCode';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[Cust_code] = CONVERT(TINYINT, ' + CONVERT(NVARCHAR(5), @Cust_code) + N')';
END
 
-- These are highly limited sets
IF (@CustomerID IS NOT NULL)
 SET @Recompile = 0
 
IF (PATINDEX('%[%_?]%', @LastName) >= 4
 OR PATINDEX('%[%_?]%', @LastName) = 0)
 AND (PATINDEX('%[%_?]%', @FirstName) >= 4
 OR PATINDEX('%[%_?]%', @FirstName) = 0)
 SET @Recompile = 0
 
IF (PATINDEX('%[%_?]%', @EmailAddress) >= 4
 OR PATINDEX('%[%_?]%', @EmailAddress) = 0)
 SET @Recompile = 0
 
IF @Recompile = 1
BEGIN
 -- Use this next line for testing
 -- SELECT @ExecStr -- For testing
 EXEC (@ExecStr);
END
ELSE
BEGIN
 -- Use this next line for testing
 -- SELECT @spexecutesqlStr, @Lastname, @Firstname, @CustomerID;
 EXEC [sp_executesql] @spexecutesqlStr
 , N'@CustID bigint, @LName varchar(30), @FName varchar(30)
 , @MI char(1), @Email varchar(128), @RegionNo tinyint
 , @CustomerCode tinyint'
 , @CustID = @CustomerID
 , @LName = @LastName
 , @FName = @FirstName
 , @MI = @MiddleInitial
 , @Email = @EmailAddress
 , @RegionNo = @Region_no
 , @CustomerCode = @Cust_code;
END;
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.