Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krish087
New Member

executing stored procedure

I am trying to execute Stored procedure and fetch results, I am getting error can any one help?

4 REPLIES 4
arelf27
Helper II
Helper II

I'm trying to execute a simple stored procedure and getting error: "the EXECUTE permission was denied on object 'stored proc name', database 'database name', schema 'dbo'."

 

Stored proc is just this:

USE [DB name]
GO

CREATE PROCEDURE [dbo].P_x AS

SELECT 'x' as name
go

 

I am able to use views from this same db no problem and have created a number of reports, dashboards, etc. however for the life of me can't figure out how to use a stored proc instead of a view..

 

To connect inside PowerBI I use the following syntax:

SELECT *
FROM OPENQUERY ([server_x],
'EXEC db_x.dbo.P_x');

@arelf27 What is the reasoning for using the OPENQUERY?

You are all ready setting the connection to the database, if you just use "EXECUTE dbo.P_x" it should run without any issues.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

I believe I need to do openquery as I'm using DirectQuery not Import option. From what I read openquery should work for either.. I did try just execute dbo.p_x and it worked to where I could see the resultset of my stored proc and even the sequel statement appeared fine and said there are no syntax errors, however strangely enough, when I pressed close & apply it says there's a syntax error....so had to go back to query window and delete the query.. I think it's because of the DirectQuery.. I mean otherwise what would be the mechanism of re-running the stored proc for updated resultset? (in other project where I'm using Import I am able to execute the stored proc per the method you mention and it works fine...I just really want to stick to DirectQuery for this project)

Greg_Deckler
Super User
Super User

What is the error that you are getting? Is it something along the lines of "not authorized for execution"?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors