Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase 123»»»

Is the Schema Still Needed? Expand / Collapse
Posted Thursday, April 24, 2003 12:00 AM



Group: Administrators
Last Login: Thursday, July 30, 2015 4:36 PM
Points: 32,253, Visits: 16,604
Comments posted to this topic are about the content posted at

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #11677
Posted Sunday, May 18, 2003 5:45 PM


Group: General Forum Members
Last Login: Saturday, July 7, 2012 4:08 PM
Points: 21, Visits: 55
Outlived its usefulness? Heck, I'd be happy if somebody told me what its usefulness ever was. I have never understood the desire to have multiple objects with the same base name in the same database. What is or ever was the benefit?

Post #60491
Posted Sunday, May 18, 2003 6:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 26, 2010 7:51 AM
Points: 269, Visits: 8
Only being able to speak from personal experience, that is what I will speak from. It is not a feature that I have ever used and the only reason to know about it is so to be sure that an oversight doesn't allow it to cause problems.

Post #60492
Posted Sunday, May 18, 2003 7:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 14, 2015 8:59 PM
Points: 1,038, Visits: 445
Up until about two months ago, I thought it was totally silly and had very little use... But, one, I hope "clever" <g>, of them did spring to mind.

I've been writing a doc management system which is to have very tight security, etc, etc. The customers can write their own custom reports which can be based upon anything in the system. In order to ensure that someone cannot run and report and see something they should not normally see, all users of the system have their own SQL login (which my app will create/destroy/modify as required). Each user will have their own set of views (all with the same name, only distinguished by the schema) which map pretty much map directly to each normal table in the database, except for a discrimination on each view's where clause to exclude rows that view's owner cannot see.

Thus, when a report is written, just the ordinary view names are used (no schemas) - then when an ordinary pleb logs in, they cannot see the additional records that the super user who wrote the report can see. No changes required to the report at all!!

I've done a bit of testing on the above and it seems to work quite well.... Anyone else think of a "good" reason for them??? Particularly when MS bring forth row level security in Yukon (apparently?), the above use will diminish too (does anyone else do things this way or have I missed something)

My 2c.. Cheers,

Post #60493
Posted Sunday, May 18, 2003 8:32 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
Hi Steve

Good point to bring up actually.

This sort of concept comes from the oracle world, but were see a clearer distinction between "user" and "owner" of objects. Here in oracle an instance = a single db with 1 or more "schemas", where schemas can be similar to a single sql server instance with a bunch of user databases. This is purely thinking aloud though and there are other issues to consider of course..

In SQL Server best practice stats "all objects owned by dbo"... so other "schema" users are irrelevant. I tend to disagree, esp during development where people can play around with their own objects and we cut them over to dbo and wrapper the security model around them to formalise the structure/framework of the user objects in the db.

There are some other cases where I like to break the dbo rule, and have a different schema owner for my audit tables.. eg:


why? I can still wrapper up all my security privs etc, but at the same time its easily to single out my audit tables and keep them alphabetically listed. Of course, alphabetic listing is not the only reason... :)

The BIG problem with non-dbo schemas tends to be db import/export via DTS and its inability to match up obj names between schema owners.



Chris Kempster
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #60494
Posted Sunday, May 18, 2003 9:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 11, 2005 1:42 AM
Points: 1, Visits: 1
if you go back to the fundamental justifications for having databases (right back to the days hierarchical Dbs for instance) the whole thrust was about data management approaches that would break down isolated "islands of information" that bedevilled the corporate landscapes and caused no end of inconsistencies and confusions.
The schema concept being challenged here came to the relational world in Oracle's products - it was a pretty silly idea then and frankly it still is - because it tends to reinforce the "islands" type thinking. But hey, I won't just bash Oracle for it - if there was a stupid idea lying around then for sure M$ would just have to adopt it too now wouldn't they?

Post #60495
Posted Monday, May 19, 2003 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2004 2:54 AM
Points: 6, Visits: 1
I vote thumbs down to schemas, for me they're just a problem I could do without (ever seen what happens if someone is the owner of a DB but is not a sysadmin?).
They also add a minimum of 4 useless characters (including the dot) to names, and given that SQL is full of stupid windows that can't be resized (e.g. many wizards) this another nuisance aspect....

Post #60496
Posted Monday, May 19, 2003 2:47 AM


Group: General Forum Members
Last Login: 2 days ago @ 11:44 AM
Points: 2,997, Visits: 2,043
I liked the idea of developing and testing with your own "schema" before cutting across to a dbo implementation.

It is not a facility that I commonly use and I don't know many other people who do. The problem is that if you work in a shop where no-one else uses a facility then who do you learn what the correct use of that facility is?

There are two uses for this facility that spring to mind.
1. Holding personalised settings for an app a'la windows profiles.
2. A centrally managed app where the data and/or other objects have to be isolated according to the user.

These days the sort of isolation and security that is carried out at the application level rather than the database level.

In the case of point 2 its a case of "an" answer rather than "the" answer.

LinkedIn Profile
Newbie on
Post #60497
Posted Monday, May 19, 2003 8:15 AM

SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 27, 2015 9:11 AM
Points: 657, Visits: 214
I'd vote a general thumbs-down as well--but they do have their uses.

The primary negative point is Confusion. Schemas are a pretty abstract concept (the closest analogy I can come up with is "parallel dimensions applied to database tables"), and how can you clearly and simply explain schemas to the people who may be required to use them--especially if they're not even clear on why a table is (you'll note I didn't say "what").

Managing them is also confusing. How many schemas can there be in a database? Can you readily and ably manage them and their contents as a coherent set? I don't know anything about this subject, as once I realized what they were I made sure they weren't going to be used.

Lastly, there's the (at least to me) very confusing and harshly underdocumented subject of optimization. In all code (stored procedure or otherwise), "best practice" is you're supposed to qualify all objects with their full ownership chain (i.e., "SELECT * from myDB.dbo.myTable", not "USE myDB" | "SELECT # from myTable"). If so, what's the point of setting context (USE mdDB)? What happens if you have multiple database instances with different names? Or is that just for stored procedures (and which is better, "EXECUTE myDB.dbo.myProc", "EXECUTE dbo.myProc", or "EXECUTE myProc"? I know this underlies optimizing procedure cache hits and utilization, but I've never gotten the long or short of it.)

I do like the idea of setting up "your own set of objects" (your own pocket universe) for development purposes... but again, how easy is it to explain this to the developers? How easy is it to set everything to .dbo when it's time to roll it to a QC environment? How many problems and glitches will arise solely from the fact that everyone was working in their own schema? Me, I just create and destroy a separate copy of the database whenever I need to do something like this... but I'm the DBA and can get away with it. Others are not so lucky.

Now, if it were possible to define or configure a distinct set of schemas (as opposed to one for everyone who can log on with no questions asked), and permit or deny individuals access to them, that might have some benefit. (It's only a thought--I can see this quickly spiraling out of control.)

This should be an interesting thread. Enough two centses add up to dollars...


Post #60498
Posted Monday, May 19, 2003 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2007 1:48 PM
Points: 42, Visits: 1
I agree with the article.

Regarding the ownership issue, we provide a separate copy of a database for each developer who needs one to run and test their changes on their own machine. Changes to the production databases are controlled by the DBA group so that the only schema is dbo.

User preferences are done by attaching user identification columns to the appropriate tables where necessary. Users are not allowed to create objects in the database.

I agree with the KISS principle as well.

James Brake
Associate Electric Cooperative, Inc.

Post #60499
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse