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.
Aaron's suggestion of using
EXECUTE AS OWNER does not work because
Owner doesn't have access to DatabaseB.
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.