Executing Stored Procedure that accesses another SQL instance

Try using EXECUTE AS LOGIN = 'DOMAIN\username' instead and see if that works.

Take a look at using EXECUTE AS + Trustworthy . You can set it up where it can be called within the stored procedure as long as user b has been given access and the two databases trust each other.

This guys blog should answer or provide everything you need. http://www.sommarskog.se/grantperm.html#EXECAScrossdb

http://msdn.microsoft.com/en-us/library/ms188304%28v=sql.90%29.aspx

After reading extensively on the topic and doing a number of experiments, I believe I have come to a conclusion on this matter. The EXECUTE AS statement is not designed to work cross-instance without major security implications. What I was hoping for was a way to tell my procedure what Windows identity I wanted to run under, since a Windows identity can have access to multiple resources on multiple servers. However, even after playing around with a bunch of different settings, it became apparent that I would have to weaken other security measures in order to allow a stored procedure to impersonate me.

There doesn't seem to be a lot of information out there about cross-instance or cross-server procedures. I would imagine the reason for this is because of the security and performance implications of doing so. However, I believe there are cases where it is important and it seems like the solutions to doing so are complicated and very scenario-specific. I came across a good article that helped me at least understand some of my options. It wasn't focused on cross-instance access but it did give me the clues I was looking for. I would encourage you to check it out:

http://www.sommarskog.se/grantperm.html

I would still be interested in other solutions to this problem, but my solution right now is two-fold. First, if I absolutely need to access two databases via one stored procedure, I have to use a Windows login. I avoid this whenever possible, however, since it does cause performance issues (multi-server locking, network complications, inability to optimize the query, etc.) Second, I bring the data from each database through separate, database-specific calls. That means I bring the data back to the client before merging it. It isn't as performant or as clean as I would like, but it seems to be the safest solution.