topic Re: The EXECUTE permission was denied on the object in QlikView

The EXECUTE permission was denied on the object

wdchristensen — Thu, 22 Mar 2018 18:52:15 GMT

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.

Re: The EXECUTE permission was denied on the object

sasiparupudi1 — Thu, 22 Mar 2018 19:40:47 GMT

‌could you paste your Qlik script here..?

Re: The EXECUTE permission was denied on the object

wdchristensen — Thu, 22 Mar 2018 20:01:42 GMT

LIB CONNECT TO 'CnMyServer';

Denominator:

NoConcatenate

LOAD *;

SQL exec mydatabase.dbo.myStoreProc;

Re: The EXECUTE permission was denied on the object

wdchristensen — Thu, 22 Mar 2018 20:02:51 GMT

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

Re: The EXECUTE permission was denied on the object

sasiparupudi1 — Thu, 22 Mar 2018 20:12:47 GMT

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

Re: The EXECUTE permission was denied on the object

wdchristensen — Thu, 22 Mar 2018 20:15:21 GMT

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]

Re: The EXECUTE permission was denied on the object

wdchristensen — Thu, 22 Mar 2018 20:44:44 GMT

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!

Re: The EXECUTE permission was denied on the object

Anonymous — Wed, 04 Jul 2018 12:02:20 GMT

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

Re: The EXECUTE permission was denied on the object

FedericoC — Mon, 29 Jul 2024 13:58:37 GMT

with EXEC grant I solved the same problem. Thanks