I have created a stored sql procedure to run within a MS-Query. The problem I have is that I can pass the parameters from Excel to the SP without a problem. However, the end user cannot becuase they do not have the proper permissions on the SQL server.
In another recent Experts Exchange answer to another question I was given the line of code:
"with execute as owner" to alow the user to emulate my privileges.
Here is my procedure code:
USE [livedb]
GO
/****** Object: StoredProcedure [dbo].[GetIPAProcCnt_Summary] Script Date: 11/16/2014 11:00:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Glen Powers
-- Create date: November 16,2014
-- Description: IPA Procedures Summary by Surgeon and Date
-- =============================================
ALTER PROCEDURE [dbo].[GetIPAProcCnt_Summary]
-- Add the parameters for the stored procedure here
@Datefrom datetime,
@Dateto datetime
with execute as owner
As
SELECT TOP (100) PERCENT dbo.AbsOperationSurgeons.ProviderID, dbo.AbsOperationProcedures.ProcedureCode,
dbo.AbsOperationProcedures.ProcedureCodeName, SUM((CASE WHEN dbo.BarVisits.InpatientOrOutpatient = 'I' THEN 1 ELSE 0 END)) AS IPCNT,
SUM((CASE WHEN dbo.BarVisits.InpatientOrOutpatient = 'O' THEN 1 ELSE 0 END)) AS OPCNT
FROM dbo.AbsOperationSurgeons LEFT OUTER JOIN
dbo.BarVisits ON dbo.AbsOperationSurgeons.VisitID = dbo.BarVisits.VisitID RIGHT OUTER JOIN
dbo.AbsOperations ON dbo.AbsOperationSurgeons.VisitID = dbo.AbsOperations.VisitID LEFT OUTER JOIN
dbo.AbsOperationProcedures ON dbo.AbsOperations.VisitID = dbo.AbsOperationProcedures.VisitID
WHERE (dbo.AbsOperations.DateTime >= @Datefrom) AND (dbo.AbsOperations.DateTime <= @Dateto) AND(dbo.AbsOperationProcedures.ProcedureCode IS NOT NULL) AND
(dbo.AbsOperationSurgeons.ProviderID IS NOT NULL) AND (dbo.AbsOperationSurgeons.ProviderSeqID = 1)
GROUP BY dbo.AbsOperationSurgeons.ProviderID, dbo.AbsOperationProcedures.ProcedureCode, dbo.AbsOperationProcedures.ProcedureCodeName
ORDER BY dbo.AbsOperationSurgeons.ProviderID, dbo.AbsOperationProcedures.ProcedureCode
Here is the error message I get:
Msg 15517, Level 16, State 1, Procedure GetIPAProcCnt_Summary, Line 0
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
It looks like I cannot get around this roadblock.
The Stored Procedure runs correctly without the "with execute as owner" when I execute the stored procedure from the Object Explorer.
Is this error correctable?
Thanks
glen