CODEWITHSUNDEEP
sql-serversql-server-2012linked-server
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

Unable to call stored procedure from linked server

I am facing a strange issue. I have a linked server on ServerB for ServerA

Now, when I am calling a stored procedure from ServerB like

EXEC [ServerA].[Db].[dbo].[SpName] @Param1 @param2

I am getting error that

The EXECUTE permission was denied on the object 'SpName', database 'Db', schema 'dbo'.

But now when I am executing below query it is returning me result:

SELECT * 
FROM [ServerA].[Db].[dbo].[tblName]

I don't know that why I am not able to execute stored procedure from ServerB. I am Db_Owner on both the server.

Screenshot of Linked server security

enter image description here

Linked server catalog

enter image description here

Upvotes: 2

Views: 9712

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

If a linked server query fails, the things to check are (in rough order of probability):

  • Try logging in locally on the linked server to test access directly. If you have no local access, obviously you won't have it through the link either.
  • Verify the correct credentials as used when accessing the linked server, and not another user you're not expecting. You can check this with EXEC ('SELECT USER_NAME(), SUSER_NAME()') AT [Server]; if the user name is not what you're expecting, check your linked server definition for the correct login mappings. If you can't access the server at all (any query fails), you have other problems (like Kerberos authentication issues if you're using integrated authentication).
  • Perform a sanity check that you're accessing the correct server with EXEC ('SELECT @@SERVERNAME') AT [Server]. The network name of a linked server can be changed using sp_setnetname, so the name you use to a access the server isn't necessarily the machine name.
  • If all else fails, dropping and recreating the linked server definition is always an option, but obviously this could disrupt production work.

Upvotes: 3

Related Questions