Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

The EXECUTE permission was denied on the object

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. 

1 Solution

Accepted Solutions
Master III
Master III

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

View solution in original post

8 Replies
Master III
Master III

‌could you paste your Qlik script here..?

Specialist
Specialist

LIB CONNECT TO 'CnMyServer';


Denominator:

NoConcatenate

LOAD *;

SQL exec mydatabase.dbo.myStoreProc;

Specialist
Specialist

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

Master III
Master III

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

View solution in original post

Specialist
Specialist

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]

Specialist
Specialist

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! 

Contributor
Contributor

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

Contributor
Contributor

Hello,

The EXECUTE permission was denied on the object 'sp_Example', database 'db', schema 'dbo'. - User has not permission to run “sp_Example” stored procedure in  db database. If you are database admin then you need to give permission to the user performing the execution like below.

he database administrators recommend to create a new database role like db_executor.

CREATE ROLE db_executor;

–Grant that role exec permission.

GRANT EXECUTE TO db_executor;

To add this new permission to user you need to:

  • Go to the properties of the user
  • Go to User Mapping
  • Select the database where you have added new role
  • New role will be visible in the Database role membership
  • Check db_executor role and save

Check more on this source about EXECUTE permission.

Thnx