0

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

3
  • Instead of creating different databases per customer, you can use SCHEMA to logically seperate the data. This way you just give access to a particular schema to your customer. I have answered How To Hide Schema from user and Setting user permissions for different SQL Server schemas. One database per customer will make sense when you have different availablity requirements per customer. – Kin Shah Jul 6 '15 at 3:08
  • Wouldn't I have the same problem though if the view in the schema I give them access to selects data from another schema (say the dbo schema) wouldn't I still have to give them access to the dbo view in order for that view to execute? – eejai42 Jul 6 '15 at 3:36
  • Basically - I'm trying to give someone access to a view, but not the tables it's based on. Is this possible? Maybe that's a more concise way to say what I'm trying to do, but I'm not sure. – eejai42 Jul 6 '15 at 3:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.