0

I have created a stored procedure on ServerA (SQL Server 2019) which inserts a number of rows in a local table.

When I call the stored procedure using OPENQUERY from ServerB (SQL Server 2012), the procedure runs but the rows are not inserted.

Performing the same insert from ServerB with OPENQUERY returns an error 7357 (the actual error message is in French, saying that the OLE DB provider "SQLNCLI11" reports that the object has no columns or the user has no access to the object).

I'm using a linked server on ServerB with SQL authentication. RPC is disabled on ServerA, hence the use of OPENQUERY.

If I use the same statement to insert using OPENQUERY in an SSMS session directly on ServerA using the same credentials as the linked server, it works - I had granted INSERT to this user on the table in question before starting my tests.

I have performed updates on the same table from the linked server for many months without problems.

In fact, an INSERT on ServerB using a 4-part name for the table works.

Is there an extra permission I need to perform inserts remotely?

3
  • Please add the exact OPENQUERY() statement to your Post.
    – J.D.
    Nov 14, 2023 at 13:24
  • 1
    "RPC is disabled on ServerA". Enable RPC OUT on the linked server definition and exec the stored procedure directly or with EXEC ('whatever') at ServerA. Nov 14, 2023 at 15:03
  • 1
    @DavidBrowne-Microsoft Thank you. That worked. Nothing I've found here or elsewhere explained that I could enable RPC OUT on ServerB without touching the configuration on ServerA. Please would you post as an answer.
    – grahamj42
    Nov 14, 2023 at 15:32

1 Answer 1

2

"RPC is disabled on ServerA".

RPC and RPC OUT are settings on the linked server definition, not on the target server.

So just enable RPC OUT on the linked server definition and exec the stored procedure directly or with

EXEC ('whatever') at ServerA

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.