GPSPOW
asked on
Running a Stored Procedure from MS-Query
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_Summa ry] 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_Summa ry]
-- Add the parameters for the stored procedure here
@Datefrom datetime,
@Dateto datetime
with execute as owner
As
SELECT TOP (100) PERCENT dbo.AbsOperationSurgeons.P roviderID, dbo.AbsOperationProcedures .Procedure Code,
dbo.AbsOperationProcedures .Procedure CodeName, SUM((CASE WHEN dbo.BarVisits.InpatientOrO utpatient = 'I' THEN 1 ELSE 0 END)) AS IPCNT,
SUM((CASE WHEN dbo.BarVisits.InpatientOrO utpatient = 'O' THEN 1 ELSE 0 END)) AS OPCNT
FROM dbo.AbsOperationSurgeons LEFT OUTER JOIN
dbo.BarVisits ON dbo.AbsOperationSurgeons.V isitID = dbo.BarVisits.VisitID RIGHT OUTER JOIN
dbo.AbsOperations ON dbo.AbsOperationSurgeons.V isitID = dbo.AbsOperations.VisitID LEFT OUTER JOIN
dbo.AbsOperationProcedures ON dbo.AbsOperations.VisitID = dbo.AbsOperationProcedures .VisitID
WHERE (dbo.AbsOperations.DateTim e >= @Datefrom) AND (dbo.AbsOperations.DateTim e <= @Dateto) AND(dbo.AbsOperationProced ures.Proce dureCode IS NOT NULL) AND
(dbo.AbsOperationSurgeons. ProviderID IS NOT NULL) AND (dbo.AbsOperationSurgeons. ProviderSe qID = 1)
GROUP BY dbo.AbsOperationSurgeons.P roviderID, dbo.AbsOperationProcedures .Procedure Code, dbo.AbsOperationProcedures .Procedure CodeName
ORDER BY dbo.AbsOperationSurgeons.P roviderID, dbo.AbsOperationProcedures .Procedure Code
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
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_Summa
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_Summa
-- Add the parameters for the stored procedure here
@Datefrom datetime,
@Dateto datetime
with execute as owner
As
SELECT TOP (100) PERCENT dbo.AbsOperationSurgeons.P
dbo.AbsOperationProcedures
SUM((CASE WHEN dbo.BarVisits.InpatientOrO
FROM dbo.AbsOperationSurgeons LEFT OUTER JOIN
dbo.BarVisits ON dbo.AbsOperationSurgeons.V
dbo.AbsOperations ON dbo.AbsOperationSurgeons.V
dbo.AbsOperationProcedures
WHERE (dbo.AbsOperations.DateTim
(dbo.AbsOperationSurgeons.
GROUP BY dbo.AbsOperationSurgeons.P
ORDER BY dbo.AbsOperationSurgeons.P
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade. When I wrote the article my client was an airline, and nothing was considered confidential, so security wasn't an issue.
With almost every other client, including my current healthcare one, I've always needed two things to make this work: (1) Business Users who will sit down and 'own' the groupings of various roles, and which business users are in that role, and (2) In absence of the developer being able to do this a DBA willing to take that list, create the roles, and assign groups to roles.
Good luck.
Jim
With almost every other client, including my current healthcare one, I've always needed two things to make this work: (1) Business Users who will sit down and 'own' the groupings of various roles, and which business users are in that role, and (2) In absence of the developer being able to do this a DBA willing to take that list, create the roles, and assign groups to roles.
Good luck.
Jim
ASKER
I will have notify my client to see if they are willing to add executive privileges to the end users so they can run the SP's.
In the past they have been reluctant.
Glen