executing stored procedure

[krish087]

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

[arelf27]

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');

[Seth_C_Bauer]

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

[arelf27]

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]

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