Execute query to linked server as another user

Question

I'd like to execute a query which uses linked server as a specific user. However, not even a simple example works.

When I opem SSMS as user "domain\user", connect to "serverA" and run following code:

EXECUTE ('SELECT col FROM serverB.dbB.dbo.table')

it works fine.

However, when I opem SSMS as user "domain\admin", connect to "serverA" and run following code:

EXECUTE ('SELECT col FROM serverB.dbB.dbo.table') AS LOGIN = 'domain\user'

It throws following error:

Linked servers cannot be used under impersonation without a mapping for the impersonated login.

I've googled a lot, but haven't found any solution. Does anybody know where the problem can be?

Answer 1

When you are creating a linked server you can enter a remote username and password and map a local user to the remote database.

However you can't use this technique to map to a domain account, the user and password use SQL server authentication.