I apologize if this question repeats another already asked. I have searched for hours and have not found one that fits my situation.
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).
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.