SQL Server how to control access to data between clients on the same database

Question

Hi,

We have a system with 2 clients (which will increase). These two clients connect to the same server/database, however neither should be able to see the others sensitive information. There is however some shared non sensitive information.

There is also an administrative department who does work on behalf on both of the clients. They are allowed to see all sensitive data.

We currently handle this by holding a ClientID against the tables in question and with a mixture of views and queries check against the ClientID to control access for each client.

I want to move to a consistent handling of this in our system e.g. all views, or all queries, however I just wondered if there was perhaps an easier/ better Pattern than using views to handle this situation?

We're using Sql Server 2005 however upgrade to 2008 is possible.

Cheers

Answer 1

the most logical way is to have (indexed) views filtered by what each user can see. add read/write permisisons to each client for their views. admins access the tables directly.

but it looks to me that each client is a logicaly separated entity form the others. if that's the case you might consider having 1 db per client and 1 db for shared stuff. admins can access everything, each client cas only access it's own db and read from common db.

a 3rd option is to look into schemas and separate your clients there.