2

My company uses a multi-tenant database (one database, all tables have a customerID field).

We are in the process of setting up a data warehouse and giving clients direct access. Our initial thoughts on architecture are to create a schema for each customer, with views in each schema that providing the filtering by customer ID.

i.e.

Main Database

dbo.Table1

Schema1

schema1.Table1 (View) Select * from dbo.Table1 where custId = '1'

This works, except the user with rights to schema1 can ALSO do a select all from dbo.Table1. I would like to prevent this.

Is that possible?

Improve this question

1 Answer 1

Reset to default
5

Yes. Simply grant the user that you create that lets them log into the SQL Server SELECT rights only on the view and not the table.

You could actually do all this through a single view.

Create a function that looks up the CustomerId based on the username. Then call that function as part of the view.

create view YourView as 
select * 
from YourTable where CustomerID = dbo.YourFunction(suser_sname())
Improve this answer
2
  • Thanks. My problem was that I added the user to db_datareader in addition to giving the schema select rights.
    – Jason Watts
    Commented Jul 8, 2011 at 23:48
  • If they have db_datareader then they can ready everything. You don't want to do this. You would need to specifically DENY them rights to everything you don't want them to have access to. You should remove them from db_datareader and specifically grant them rights to the objects that they need.
    – mrdenny
    Commented Jul 9, 2011 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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