[dba-SQLServer] Schemas for separation of clients?

[John W. Colby]

Francisco, thanks for replying to this discussion.  I worked at IBM in 
large databases.  The way they used schemas was that the schema allowed 
creating anything you wanted (tables, views, SPs etc) which were 
assigned to a specific schema.  The result was a horrendous mess simply 
because inside each database was a ton of stuff for each schema.  
Imagine in this case that you want identical stuff for each client, same 
tables, same views etc. and that there are 500 items total (tables / 
views / SPs etc).  Each client would have their own COPY of each item.  
If you fix one, you ONLY fix the item for that schema.  If you have 100 
clients..... imagine the quantity of items in the database container.

Understand that I did not get into the hows and whys, just the results.  
The result was a mess.  At IBM, schemas appeared to be used to group 
what various employees could see, not what clients could see.  Since the 
quantity of employee "groups" was quantifiable, the result did kind of 
work, though it was still messy.  IIRC, the schemas could be assigned to 
login groups to limit ACCESS to various items.  It was all rather 
confusing to me but I was not tasked with understanding and implementing it.

I do not understand how a schema could be used in the context of an 
infinite (or at least ever expanding) number of clients.

I certainly don't have an answer for this problem but I am not 
convinced, given my run-in with schemas that they are that answer.

You are the guru here though so I am interested in your take.

John W. Colby

On 3/31/2015 9:42 AM, fhtapia at gmail.com wrote:
> Hello Mark,
>    If the intent is to provide a segregated security space for multiple
> users, then Schema's offers a huge advantage.  They are simply containers
> to an entire database set per grouping.
>
> It is much simpler to set security based on the schema, than to focus on
> the individual db Table.  not to mention, the peril of accidentally
> returning data back to a customer that is not their own because the
> query/sproc had access to any data in the table.
>
> more solid points:
>
> http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/
>
> --
> Francisco
>