Sql-server – SQL Server Schema Security

schemaSecuritysql-server-2008

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?

Best Answer

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())
Related Question