I get a strange behaviour when I combine impersonation with database links in SQL Server 2005. First, I connect to a database server using simple SQL Server Authentication with Login 'John'. On this server, a server link remote_sqlserver is defined. I already have SELECT privileges for mydb in this server. When I simply query a table on a DB on this server link:
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Works!
After that, I try impersonation with the same Login (don't ask why would one do that, I'm just experimenting ;) )
EXECUTE AS LOGIN = 'John'
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Error: "Login failed for user: 'John'"
When I revert, it works again:
REVERT
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Works!
Do you have any idea, why I get an error with impersonation, although the same Login can query the table without impersonation?
BTW: After "impersonation as self", if I query a local database, (of course, for which I have enough privileges) I don't get any error. It only happens when I query a remote DB via server link.
You should read the Books Online article "Extending Database Impersonation by Using EXECUTE AS".
When you use EXECUTE AS to access a remote server, the remote server has to be configured to trust the caller. Even though the the "parent" and "child" logins are the same (i.e. "John") since you are using EXECUTE AS the trust relationships have to be set up. The authentication "path" is different even though the login is the same.