Schemas and SSMS - Looking to vent and understand

Last Post 04 Aug 2008 06:21 AM by Dan W. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Dan W
New Member
New Member

--
14 May 2008 01:44 PM
Question I'm puzzled by:
Why are schema's made in a new database in SQL Server 2005 to match the names of the built-in database roles, such as db_datareader?

I've been searching, but not finding, so I thought I'd try here.

Venting (is anyone else frustrated by this?):

I get the concept of user-schema separation in SQL Server 2005. For the most part, I think it was a great and much needed addition to SQL Server. It make it feasible to use something other than dbo for the third part of a four-part name. I still haven't really used a schema other than dbo, but I think I just might use the dbo schema it as a way to grant execute permissions to my stored procedures.

What I don't get is the way the UI presents users, schemas, and roles in SSMS.

Example 1: (more about my question) If a role is a principal, and a schema is a securable (and a container for other securables), why on earth would SQL Server 2005 create a schema of db_datareader for a new database? I could see creating schema's for roles and users during conversions of 2000 databases, in case the 2000 database was using users as schema's by making users other than dbo be the owner of objects, but why do they continue such nonsense in a brand new 2005 data file, where users and roles are supposed to be separate from schema's? It just makes it confusing because the implementation doesn't seem to support what it written about the concepts! Ugh!!!
Is there a legitimate need that I just don't see?

Example 2: Specifying the owner of a schema is something done probably once in the life-cyle of a database, yet it's the most up-front part of creating a new user, which will be done throughout the life-cycle of a database. In trying to search about this stuff, it seems like the #1 problem with user-schema separation is people assigning new users as owners of a schema, presumably just wanting the new user to be member of the role that goes by the same name - since this was so easy and up-front in SQL Server 2000.

Example 3: In SQL Server 2000, if I remember correctly, when you looked at the list of members in a role in EM, you would only see the members explicity added to that role. Therefore if User1 was a member of DBROLE_1, and DBROLE_1 was a member of DBROLE_2, you wouldn't see User1 in the list of members of DBROLE_2. I like that, because it only has inherited membership, not explicit membership.
In SQL Server 2005, given the same situation, both DBROLE_1 and User1 are listed as members of DBROLE_2. This has a good side and a bad side. It's good to be able see the effective membership of a role, but it's confusing as all get-out if you try to remove an individual user because you don't want users to be explicit members of that role.
SQLUSA
New Member
New Member

--
14 May 2008 10:03 PM
quote:

Originally posted by: Dan W
Question I'm puzzled by:
It make it feasible to use something other than dbo for the third part of a four-part name. I still haven't really used a schema other than dbo, but I think I just might use the dbo schema it as a way to grant execute permissions to my stored procedures.
.


Actually I discussed this with Microsoft execs at the introductory festivities in November 2005, Las Vegas, Mandalay Bay Hotel Casino. Having a single level schema is very limiting, not much help in a large database e.g. 3000 tables.

You would need multi-level schema to properly organize your tables and other database objects in large environment.

Example: StockMarketDB.Danish:Bond.Price

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SwePeso
New Member
New Member

--
15 May 2008 04:02 AM
quote:

Originally posted by: SQLUSA
You would need multi-level schema to properly organize your tables and other database objects in large environment.

Example: StockMarketDB.Danish:Bond.Price

Oh behave!

Now you have set us back several years. Is your suggestion not the same as having separate tables with bond prices for every single country?

Create ONE table for bond prices and put a column in there to denote country origin.
Some sort of normalization, you know...


SQLUSA
New Member
New Member

--
31 May 2008 07:34 AM
quote:

Originally posted by: Pesomannen
quote:

Originally posted by: SQLUSA

Now you have set us back several years. Is your suggestion not the same as having separate tables with bond prices for every single country?




Assume you have a database Enterprise. Used by:

Automotive
Household
Consumer
Construction
Agriculture
division of Alpha Enterpises, Ltd.

Each division needs the following SHEMAS:
HumanResources
Person
Production
Purchasing
Sales
Marketing
....and much more private schemas


The divisions want to have their tables, don't want to be mixed up with others. Some of the schemas they desire are custom to that division.

The following MULTI-LEVEL SCHEMAS would satisfy the need (note this is a dream):

Enterprise.Consumer:Sales.SalesOrderHeader

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/



Dan W
New Member
New Member

--
26 Jun 2008 06:24 PM
I don't know how you made the jump from my reference to a four-part name to discussing a multi-level schema. The four-part name I spoke of is that which is referenced (but not really defined) in BOL under information about Linked Servers. The four parts I was assuming knowlege of are roughly described as server.database.schema.object, except SQL Server 2000 didn not call it a schema nor treat it like a schema, which is why I referred to it as the third part of a four-part name.

Also, the schema concept is not what I was puzzled about, as you seem to have thought from your quote. I think user-schema separation is great. I just wish that the SSMS GUI would actually represent them as two separate things, instead of continuing to do strange things to mix the two, like having a schema named db_datareader. The role of db_datareader is helpful, but there's no use to have a schema named db_datareader. I don't think anyone is ever going to have any use for create a table named something like mydb.db_datareader.mytable.

I know my original message was long for a forum discussion, but no one has responded to anything I actually said. Do the inconsistencies I pointed out go un-noticed by the rest of you, or you do you just not care about them?
Dan W
New Member
New Member

--
04 Aug 2008 06:21 AM
Pro Pete,

Thanks for the thoughtful reply, and points well taken about the delayed responses and discussing why a feature is present. I am interested in the information, but not merely for the short-term, and taking time to visit the forum is a once in while thing for me.

I never thought about using the default schema for a user as a method for controlling functionality by causing users to access different objects based on schema name alone, so thanks for explaining that to me!
You are not authorized to post a reply.

Acceptable Use Policy