The EXECUTE permission was denied on the object

[wdchristensen]

I am trying to run a stored procedure from Qlik Sense 3.2 pulling from a SQL Server 2012 database and getting the following error:

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: The EXECUTE permission was denied on the object 'sp_Example', database 'db', schema 'dbo'.

Connection information

Provider=SQLOLEDB;

Integrated Security=SSPI;

The stored procedure has SET NOCOUNT ON; Importing Data from SQL Server 2016 from Stored Procedure with a #TempTable (Temporary table)

Any ideas as to why this might be happening. My account definitely has execute permissions. Everything works great in SSMS. 

[sasiparupudi1]

‌could you paste your Qlik script here..?

[wdchristensen]

LIB CONNECT TO 'CnMyServer';

Denominator:

NoConcatenate

LOAD *;

SQL exec mydatabase.dbo.myStoreProc;

[wdchristensen]

Interestingly if I replace my stored proc with a system stored proc like sp_who the import works. 

[sasiparupudi1 · Accepted solution]

Did you try granting select permission to the user on qlik side in sqlserver?

[wdchristensen]

I have full permissions on the database but I ran the below statement for good measure.

GRANT EXEC ON mydatabase.dbo.myStoreProc TO [Domain\UserID]

[wdchristensen]

So originally I was using a connection with a service account which was missing the EXEC on the stored procedure causing the error. The strange thing is that I made a new connection with my personal credential which have full administrator permissions and replaced the connection in the load script and it was throwing the same error. I added EXEC to the service account and the load started working even though the account I added the permissions to was not the connection which was actively being used. It was as if the code didn't update and save the changes. Seems like the text in the script was showing the correct connection but the change didn't take and behind the scenes it was using a cached version of the script which was still the service account. Super strange but it is now working. Craziness! 

[Anonymous]

I was using a connection with a service account which was missing the EXEC on the stored procedure causing the error

Similar problem, I just granted "execute" to the user I'm using. For us, connecting with our domain users isn't working, for now, but that's another discussion..

[FedericoC]

with EXEC grant I solved the same problem. Thanks