Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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())
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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