April 10, 2008 at 1:12 pm
I have seen a number of posts on the site about schemas and how nice the adventureworks database looks with the tables organized nice and neatly within the schemas, but I have been trying to find out what the real pros and cons are in using them. Are they being used primarily for organization? Are people using them more to limit access to data? Or are most people still just using DBO?
Thanks,
John Walbert
April 10, 2008 at 1:41 pm
I am still new to 2005 so I don't have a lot of experience with schemas, but in my main project I have used schemas for data access and organization. I currently only have 2 data_xfer and dbo. The data in the database is being transferred from an existing system and I put any lookup, intermediate, and work objects used in this process in the data_xfer schema and will not grant access to this schema to normal system users. Data will also be transferred from this new system to the source systems (parts will still live) and any objects needed for this will go into the data_xfer schema as well.
I'll be interested to see what others are doing as well. I know that most of the DB's I have been involved in the separation of schemas would have been difficult because everything is interrelated so stuff in Customer relates to stuff in Production so you need at least select rights on all the schemas in order to work.
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
April 10, 2008 at 2:03 pm
Thanks Jack,
I am hoping a number of people will chime in on this as I have not seen many posts on real world examples of using the schemas. I assume a lot does depend on the use of the database such as a staging database may use the schemas more for organization where as a user queried database my use them more for limiting access to the data. You brought up a good point about areas that do not have a clear cut delineation between schemas and needing select across the board. I am also interested to find out if anyone has run into any big 'gotchas' or issues that they could not work around?
John Walbert
April 11, 2008 at 5:39 am
We're using them all over the place. It allows us to set up different security management processes within the same database and maintain them very easily since you can grant execute to all the procedures in a schema or grant read to all the tables in a schema (or deny the same). We also have an application that has two schemas with identical structures. Structure one is meant for unapproved data, structure two is the approved data. There's a process that validates all the data through a series of business rules then posts it into the approved structure. We're not allowed, per the business, to delete any data once it goes into the approved slot. We didn't want to have lots of dead data logically deleted instead of physically deleted, so we used this process. It's working very well so far.
"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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply