I have a DB [MainData] with the following data in [dbo].[Table1]
Customer Value1 Value2 -------- ------ ------ CustA Foo 1 CustA Bar 25 CustB Foo 22 CUSTA Baz 259 CUSTC Zoo 31
So - then, I have a DB called CustA with a view defined as follows:
CREATE DATABASE CustAData; GO USE [CustAData] GO CREATE VIEW Table1 AS ( SELECT * FROM [MainData].[dbo].[Table1] WHERE Customer = 'CustA'; )
In this way, when customer A connects to the [CustAData].dbo.Table1 view - he only sees his 3 records - and CustB and CustC's data is automatically hidden.
Then - i create a user for CustomerA which ONLY has access to database [CustAData]. The problem is that when this user executes a
SELECT * FROM dbo.Table1 I get an error that the user does not have access to the [MainData] database.
Is there any way to setup the security so that this user can ONLY access their database, which shares data (selectively) from another database without giving them direct access to the source database?
Thanks - maybe this doesn't make any sense, but it would be a very convenient way to configure this setup. Thanks in advance.