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.
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?