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.


Main Database



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?


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())
  • Thanks. My problem was that I added the user to db_datareader in addition to giving the schema select rights. – Jason Watts Jul 8 '11 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 Jul 9 '11 at 0:04

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.