Dear all,

I'm writing an internet application that uses SQL server as a database. In this database you have about 20 tables that will contain information about all customers. Suppose I have about 50 customers. A customer can select, insert and update records in the database using the internet application. When a customer select or updates records he should only be able to see or change records that only apply to him and not to another customer.

So in my sql statements I must always use a where clause that points to the correct customer (where customer_nr=1 for example).

Is it therefore better not let the customers use the 20 base tables but in stead create 20 views for each customer that already point to only that customer ?

This means that if you have 20 base tables and 50 customers you will have 1.000 views … Is there a limit on the views to use ?

What would you guys suggest ? Or any other suggestion ?

Views have advantage over direct access to the tables for security purposes. If you have direct access e.g. embedded SQL in you application, the user may be able to use Access to access the whole table.

However, provide you use stored procedures and have your security set correctly there should be no problem.