December 2, 2008 at 2:38 pm
A developer here likes to use schemas essentially as an organizational tool to group related tables together. So instead of naming tables as:
data_case, data_question, data_comment, form_answer, form_CallType, form_question ....
He's created separate schemas in the database and created these tables:
data.case, data.question, data.comment, form.answer, form.CallType, form.question ....
An account with dbo permissions will be using the application, so everything should work .... but ....
It doesn't seem like a good idea to use a security related concept of schemas and use them for organizational purposes ...
Comments .. thoughts ??
December 2, 2008 at 2:47 pm
Generic answer, but it fits, "It Depends". We use schemas in our stage databse to organize the destion tables for our imports from our LOB systems. This allows us to keep all the SIS tables together, HR tables together, FIN tables together, etc. Makes it easier as we create the various ETL processes to move the data through Stage.
December 2, 2008 at 3:07 pm
I don't see an issue with using schemas this way as long as the documentation notes how schemas are being used. I wouldn't recommend combining organization with security.
I also look at it like there is potentially some security involved in organization like that. I mean if I have edit procedures and reporting procedures and put them in separate schemas for organizational purposes it may work out for security as some people will have edit rights and some read only.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2008 at 5:46 am
We mostly use schemas for storage requirements or security, but I don't think simple organization would be an issue either. I also agree with Jack, if you have to combine security and organization, things might get weird.
For what it's worth, I wouldn't make the executing role or user 'dbo.' It only takes a few commands to give them all the access they need. While you may have to maintain that over time, avoiding the issues of database ownership and all that implies it's certainly worth the work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2008 at 9:21 am
There might be problems with it, but there probably won't.
I wouldn't do this, but it isn't one of those "never, ever, never" kind of things, it's just that I find the solution of naming tables to be good for this purpose. I don't think it solves the right thing. But it's not a horrible idea, just not one that I would consider good. (There's a big difference between "not good" and "bad".)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2008 at 10:24 am
Originally, the SCHEMA were intended as an organizational concept in SQL that could have security attached to it. Microsoft's implementation of them before SQL Server 2005 were so bound to security that it was virtually impossible to seperate the two issues. However, with 2005 it is very easy to use them for either or both (AdventureWorks is a good example of this).
In order for this to work well however, is should be architected, doucmented and well communicated, not just done haphazardly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply