Executing Stored Procedure that accesses another SQL instance I apologize if this question repeats another already asked.  I have searched for hours and have not found one that fits my situation.

Desired Outcome

A user using SQL authentication has execute permissions to Database1 on Server1 (default instance) and that is it.  The user executes a stored procedure that, as part of its process, accesses Database 2 on Server1\Instance2.  I would like it to be safe and simple (both are important).

More Info

My windows credentials has access to both instances (which are on the same server).  Therefore, I can execute the stored procedure under my login without difficulty.  However, I don't want to give the user my level of access.  I also need to use a SQL login since the user will not be on the domain.  

What I would like would be to give the stored procedure my level of access just for that procedure.  Since I am a sysadmin, that would give the user everything they needed for that procedure.  If I got that to work, I would probably create an account just for that purpose instead of using mine, but either way it would be safe since I control what the stored proc does.

I tried putting the "WITH EXECUTE AS" statement in my stored proc but I couldn't get it to take my windows login information.  When I put it in, I would get the following error upon compiling the stored proc:


  Cannot execute as the user 'domain\jdoe', because it does not exist or you do not have permission.


The user is sysadmin on both servers, like I said, so I'm not sure what more it needs.

I have looked into the following:


TRUSTED - I would rather not expose my database and this looks scary
Linked server - I don't want to give extra permissions.  I don't trust the other database to have access to my database and I don't trust my database to have access to all of the other database.
Certificates - This seems complicated and difficult.  Unless I could find a very simple way to do this and maintain it, I'm not sure it is worth the trouble.
Ownership chaining - Again, scary.  It looks like this causes more security issues when my goal is to prevent security issues.
Mirrored user - I've even created the same (different SID obviously) user on the other server instance and gave it the same password.  No go.


I feel like I am missing something obvious but I'm not sure what it is.  Since I've been banging my head against the wall all day on this, I'm probably too close to see it.  I would very much appreciate it if someone here could give me a hand or point me in the right direction.  I will say that I have read a lot of the MSDN articles (boy do I hate them - they never seem to tell me what I want to know).  What I would really like is a simple, easy to follow tutorial that walks me through how to do this.  Short of that, even a general indication of the direction I need to go would be helpful.
Try using EXECUTE AS LOGIN = 'DOMAIN\username' instead and see if that works.