[dba-SQLServer] Schemas for separation of clients?

[Jim Lawrence]

Hi Gustav:

It initially seem like a good idea but how does a huge and changing number of clients get managed...what about web exposure to a mix of online and guest accounts get managed? 

Jim

----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Discussion concerning MS SQL Server (dba-sqlserver at databaseadvisors.com)" <dba-sqlserver at databaseadvisors.com>
Sent: Sunday, March 29, 2015 3:40:37 AM
Subject: [dba-SQLServer] Schemas for separation of clients?

Hi all

Is it advisable and possible to create custom schemas in one database to to hold otherwise identical tables separated for each client?

For example, two tables: 

    client1.invoice
    client2.invoice

Then assign rights for client 1 to schema client1 only, and vice-versa for client2.

When linking the tables from Access, client1 would see only one set of tables, link these, and name them locally using the last part of the name only: invoice.

Would that be totally safe, so one client would never be able to see data belonging to other schemas?

The reason for asking is, that the current minimum size offered at Azure (B Basic) is 2 GB which for the application, I have in mind, could hold data for at least 25 clients. And the next step, S0 Standard, allows as much as 250 GB. Of course, the DTUs also Counts but usage pr. client is expected to be low.

/gustav

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com