linked server OLE DB provider "SQLNCLI11" for linked server

Hello I have 2 different SQL Server databases.

They are connected to each other via a linked server. The linked server is working fine.

When i try:

SELECT 
    * 
FROM 
    OPENQUERY
        ([192.168.1.170], 
        'SELECT MalCRM.dbo.ContractedNumber(1)')

I get the below error. How I can fix it?

OLE DB provider "SQLNCLI11" for linked server "192.168.1.170" returned message "Deferred prepare could not be completed.". Msg 229, Level 14, State 5, Line 1 The EXECUTE permission was denied on the object 'ContractedNumber', database 'MalCrm', schema 'dbo'.

Solution

Merhaba Berhan,

I tried following sql query which works perfect

SELECT * FROM OPENQUERY([myInstance], 'SELECT AdventureWorks.dbo.test(3)')

Did you check the credentials on the database function is valid? It seems to be a permission issue. Please check the SQL Server linked server security context and check if it has execute permission on the database object. On the referred tutorial, on security context you can map the source database user to an other user on the linked server. That might be the issue