[dba-SQLServer] Schemas for separation of clients?

[Gustav Brock]

Thanks Francisco, I'll mark that.

Next question is if you can manage backup and restore of individual schemas - if perhaps not easily then, at least, reliably?

/gustav

-----Oprindelig meddelelse-----
Fra: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af fhtapia at gmail.com
Sendt: 31. marts 2015 15:42
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Schemas for separation of clients?

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

On Sun, Mar 29, 2015 at 10:59 AM Mark Breen <marklbreen at gmail.com> wrote:

> Hello Gustav
>
> I would not take this approach for a few reasons.
>
> Before I start however, I see no technical reason why you cannot do 
> it.  In fact you could even keep one schema and just set permission s 
> at dB table level.  I only used multiple schemas one time and found it a pita.
>
> 1 reason is such complexity for small financial benefit.  You are 
> going against the normal use and it will be a struggle sooner or later.
>
> 2 security will sooner or later leak across the tables.  You goal is 
> segregation but you are putting everything in one dB and hoping nobody 
> makes a mistake in a year or two.
>
> 3 financially, the gain is not sufficient to justify the hoops you 
> have to jump through.  Unless the customer is paying pennies.  If the 
> budget is so low to force this approach then reconsider and use a 
> cloud VM and install SQL express and have separate dB's for everyone.
>
> With aws and azure so cheap I would avoid the unconventional dB 
> storage if possible.
>
> Hth
>
> Mark
>  On 29 Mar 2015 22:42, "Gustav Brock" <gustav at cactus.dk> wrote:
>
> > Hi Arthur
> >
> > The main reason is, that at Azure you don't pay by the instance but by the
> > database. The cheapest is B Basic at about USD 5 per month which is not bad
> > but, obviously, if that can serve perhaps 50 clients' data it's even 
> > better. An even better deal - given low usage - is the S0 Standard level at
> > about USD 14 per month for 250 GB database Space.
> >
> > As for the linking it should be quite easy; by default, a 
> > dbo.mytable is linked as dbo_mytable which you normally rename 
> > locally to mytable. So, when linking, you should link to 
> > client1.mytable, which will be given the name client1_mytable, which 
> > you rename to mytable. From that on, your application knows 
> > absolutely nothing about the tables of schema client2 etc.
> >
> > I cannot see why it shouldn't work, but I'm not a dba, indeed not 
> > regarding Azure SQL, so I'm looking for possible pitfalls.
> >
> > /gustav