Access a DB through Another DB ONLY

Question

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.

EJ

Answer 1

Turns out Cross Database Chaining was the answer (for us at least). Turning this on allowed me to give the user limited (dataread/datawriter) access to the database that only shows their data, and NOT the underlying database that actually stores everyone's data.

I appreciate the suggestions - thanks for weighing in.

As an aside - the reason I don't like using schema's is due to generally wanting to have each be assigned a default schema, and not have to qualify every table name with the schema name.

I find that for testing purposes, constantly having to either update my default schema (to simulate working as different users), or actually connecting as lots of different users who already have preset default schemas ends up being more of a headache than just logging in with Windows Auth as me (always) and just connecting to different databases to get different user's data.

This also allows me to (conceptually) have an entire database for each user - which really cleanly and nicely isolates their data.

Data liberation can then be achieved by literally just SELECT * FROM each table in the user's database.

Also - from my testing, of separating by schema or database (in SQL 2012) made no difference that I was able to detect across many hundreds of thousands of a variety of different types of operation.

If someone knows of some performance issue I'm not aware of - I'd love to know about it, but... at this point, keeping each customer's data in their own database is the right answer for us.

Thanks again. Good night.