patternsqlMinor
Executing Stored Procedure that accesses another SQL instance
Viewed 0 times
storedexecutingaccessessqlprocedureinstancethatanother
Problem
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:
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.
- Owners
Solution
Take a look at using
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
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
Context
StackExchange Database Administrators Q#11832, answer score: 3
Revisions (0)
No revisions yet.