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.

4 Answers 4


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

  • I tried that but that command is not designed for inside a stored procedure, evidently. Jan 28, 2012 at 20:47
  • It should work just fine inside a stored procedure. Does your account have it's own login created, or are you getting your rights via a group membership?
    – mrdenny
    Jan 30, 2012 at 23:12
  • My account has its own login, which has sysadmin rights. It also has Domain Admin rights through group membership, so that should give me everything I need and it does when I am logged on using my Windows credentials. However, I found out two things. First, if I use your above code in the WITH statement of a stored proc, it gives me a syntax error. If I put it in a statement, it will work inside of one instance but not cross-instance. Feb 1, 2012 at 15:02

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

the use of the TRUSTWORTHY database property to control access to resources outside the scope of the source database


  • The problem I see with that is that Trustworthy establishes a trust relationship between the two databases. This can be exploited by sysadmins on either side. I don't want this. I am trying to limit the permissions one person has. If I end up giving another person even more permissions, that won't be a good thing. Thanks though. Feb 1, 2012 at 15:17
  • Note here that individual owners do not have to be physical persons, but it could be a generic login for each database. You do not have to grant the entire database over. If you don't trust the sysadmins on the other database then insist on certificate signing. Feb 1, 2012 at 17:00
  • You mentioned you came across the link sommarskog.se/grantperm.html in your answer below. That is the same blog I posted in the answer I suggested. "This guys blog should answer or provide everything you need. sommarskog.se/grantperm.html#EXECAScrossdb" Maybe you were just re-referencing for others. I agree it is a good blog and read. Good Luck! Feb 1, 2012 at 17:03
  • Yeah, sorry I forgot to say that the link came from you. It was a good resource. Thanks for your help. Feb 2, 2012 at 2:31

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:


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.


If you need to accesses database objects between two SQL server Instances, I would recommend one of the following:

  1. Create and use a Linked Server between the two instances with the appropriate permission to access the object on the destination (remote) instance.
  2. Use SSIS and invoke the package from the source instance of SQL Server. Depending on which version of SQL Server used, you can have either a SQL Agent job (not schedule to run, but called by the stored procedure) or use the SSISDB to invoke the SSIS package that will access the database object on the remote instance.
  3. Move the logic to the middle layer (or the client application side)
  4. Create a CLR to access the remote database object Of these I would probably use the CLR to access the remote instance server and run execute the stored procedure. You will need to grant the account that the Source SQL Server Instance runs under access to the Remote SQL Server Instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.