Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from serverfault.com Jan 28 '12 at 3:58

This question came from our site for professional system and network administrators.

3 Answers

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

share|improve this answer
    
I tried that but that command is not designed for inside a stored procedure, evidently. –  BiggsTRC Jan 28 '12 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 '12 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. –  BiggsTRC Feb 1 '12 at 15:02
add comment

Take a look at using EXCUTE 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

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

share|improve this answer
    
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. –  BiggsTRC Feb 1 '12 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. –  SoftwareCarpenter Feb 1 '12 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! –  SoftwareCarpenter Feb 1 '12 at 17:03
    
Yeah, sorry I forgot to say that the link came from you. It was a good resource. Thanks for your help. –  BiggsTRC Feb 2 '12 at 2:31
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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