SHOWPLAN permission denied in database 'AdventureWorks'.

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.

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

Select allOpen in new window

--

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

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]

Select allOpen in new window

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'.

Experts, I've tried all solutions but does not work, we can do?

It was not solved

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;