[dba-SQLServer] Schemas for separation of clients?

[Arthur Fuller]

Gustav,

I don't have a precise answer for you. It's common practice in SQL Server
to create "families" of tables; the AdventureWorks sample databases are
show this ability off. But I have never tried to link to such a database
from Access, so can't even guess how the table-names would appear in
Access,

It would seem that you are imposing a task on your potential users -- all
that renaming. I'm also short on knowledge about Azure, but despite that, I
must ask, Why not create one database for each user? Would that setup
violate some constraint imposed by Azure's billing method?

/Arthur

On Sun, Mar 29, 2015 at 6:40 AM, Gustav Brock <gustav at cactus.dk> wrote:

> 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
>
>


-- 
Arthur