7

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?

dpelisek
  • 884
  • 3
  • 13
  • 22

1 Answers1

3

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.

Stephen Turner
  • 7,125
  • 4
  • 51
  • 68
  • Thanks webturner for yor answer. I do know about the user mapping possibility and about the fact, that only SQL user can be mapped. Is there no way how to execute command on linked server as another domain user? – dpelisek Apr 24 '15 at 04:21
  • 1
    Not from SQL server, it's possible from c# using impersonation. http://stackoverflow.com/questions/2822449/connect-to-domain-sql-server-2005-from-non-domain-machine/4736493#4736493 – Stephen Turner Apr 24 '15 at 10:59