Tell me more ×
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.
up vote 4 down vote favorite

We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not be able to read directly from tables on DatabaseB.

Is such a thing possible in Sql Server 2005?

Simply creating a Stored Procedure and giving the web user permissions to execute it returns an error of Access to the remote server is denied because no login-mapping exists.

I really don't want to give the website user account free access to DatabaseB tables.

Update

Aaron's suggestion of using EXECUTE AS OWNER does not work because Owner doesn't have access to DatabaseB.

Using EXECUTE AS 'UserWithPermissions' returns an error of Access to the remote server is denied because the current security context is not trusted. Looking this up online leads me to believe this is because the database is not marked as TRUSTWORTHY, so the remote database rejects the connection. I suspect it would work fine if the two database instances were on the same server.

I did not want to set the web database as TRUSTWORTHY since some of the security on it is more relaxed, so I went with Aaron's other option: Create a User on DatabaseB that only has access to a few specific views containing the data the website should access, then go into the Security Properties on the Linked Server object and setup the website user to login as the new limited user when accessing the linked server.

This allowed my website user to have limited access to specific data in the private database, without giving it access to any of the underlying tables.

share|improve this question

1 Answer

up vote 3 down vote accepted

Couldn't you have procedure A run with execute as owner or execute as 'db user tied to security account associated with linked server'? That way the rights of the caller don't have to transfer...

Or, you can create a login on the remote server that only has the ability to execute the queries you want, and add an impersonation account to the existing linked server on Server A to have the local webserver login impersonate the new login on server B.

share|improve this answer
Yeah but then the owner of the stored proc in Db1 would need to have permissions on the tables in Db2, right? I think this is a cross-db chaining issue. – Thomas Stringer Jun 18 '12 at 18:30
Well it's across servers, so db chaining isn't really going to help (I've only ever seen db chaining used in the context of multiple databases within a single instance). If the linked server is setup to use an account that the caller can't login as, but has execute privileges on the remote procedure (ideal) or select privileges on the remote tables, this should isolate the remote database from the user and also allow for the local procedure to be executed. – Aaron Bertrand Jun 18 '12 at 18:33
I tried both EXECUTE AS OWNER and EXECUTE AS 'UserWithAccess' and am getting the same error. I'm fairly sure dbo (owner) doesn't have access to DatabaseB, and I was trying to figure out if I have the syntax wrong for executing as another user... – Rachel Jun 18 '12 at 18:34
Can you explain (or show a screen shot) of how your linked server is setup (most importantly the security context screen)? – Aaron Bertrand Jun 18 '12 at 18:35
Ah I didn't see the multiple servers portion. Good catch. – Thomas Stringer Jun 18 '12 at 18:36
show 2 more comments

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.