Solved

Running a Stored Procedure from MS-Query

Posted on 2014-11-16
3
177 Views
Last Modified: 2014-11-16
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
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40445861
If a SP exists to provide data to a report, then users/roles should be granted execute privs:
-- Role
GRANT EXECUTE ON OBJECT::GetIPAProcCnt_Summary TO Sales
GO

-- Users
GRANT EXECUTE ON OBJECT::GetIPAProcCnt_Summary TO Jimbo
GO

-- Everybody (?)
GRANT EXECUTE ON OBJECT::GetIPAProcCnt_Summary TO public
GO

Open in new window


btw I have an article out there called Microsoft Excel & SQL Server:  Self service BI to give users the data they want that is a tutorial on how to connect Excel to SQL SP's, although it doesn't address security on the SP directly.  I'll have to add it.  Either way, if you like the article please click the green 'Was this article helpful?' button at the end.
0
 

Author Closing Comment

by:GPSPOW
ID: 40445916
Thank you for the documentation.

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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40446228
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question