Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When it comes to the dbo schema:

  • Is it a best practice to avoid using the dbo schema when creating database objects?
  • Why should the dbo schema be avoided or should it?
  • Which database user should own the dbo schema?
share|improve this question
    
Where did you hear this? –  JNK Dec 7 '11 at 19:40
    
Some consultant said that it is good practice to avoid dbo schema and always create user defined schemas and assign objets into these schemas. –  jrara Dec 7 '11 at 19:42
1  
I won't post this as an answer because I can't provide a complete answer, but that sounds like bunk to me. I always thought dbo was a normal schema choice since it's independent of the individual users on the DB. –  JNK Dec 7 '11 at 19:45
    
I found this statement also from Alexander Kuznetsov in the comments of this blog post: –  jrara Dec 7 '11 at 19:50
1  
Well, yeah, that was also the reason I asked this question here. –  jrara Dec 7 '11 at 19:54

4 Answers 4

up vote 14 down vote accepted

It may be a good practice because when you have other users using the database you want to be able to limit their access with schemas. For example in a database you have the following tables.

HR.Payhist
HR.Payscale
HR.Jobdesc
IT.username
IT.useraccesslevel
ENG.jobsite
ENG.trainings

As the HR director I am able to access anything in the HR schema, as the IT director I can see employees usernames and access levels. The Engineering department can see what job sites are active, etc. If dbo was the set schema for all the tables I would have a harder time segmenting out my data and providing access roles.

The idea, I believe, in SQL Server is to offer a product that can be access and queried by different departments. In reality only DBAs/DBDevs really access the database and it typically only stores application data.

It also helps with readability and manageability. At first blush I can easily identify what table holds what data and how the data is separated.

Personally I prefer defining schemas as a general practice. Remember schema is greek for plan, having a laid out schema structure helps you to plan and identify data.

share|improve this answer

If anything, dbo should be avoided because it's the default for SQL Server, it's also not descriptive at all. Like all other default names, since it's preknown it makes a hacker's life just that much easier (although if they're at the point where they're just trying to figure out your schema name you're probably already borked).

Where I work at, we use schemas to divy up the database into logical sections, and assign permissions to schemas.

For instance, we may have an inventory system with a database. The main tables might be in the inv schema. If we import anything into the database then a staging schema would be used as a part of the import process. If we have any system stored procedures that users don't need access to, we put them in an sp schema.

share|improve this answer
1  
+1 for "avoid it because its the default". Forces the users to at least think about it a little bit when they create an object. –  BradC Dec 7 '11 at 21:49

I think this really comes down to user preference as there's no real technological reason to do this. In fact, for simplicity sake, I say always use dbo unless your security requirements stipulate otherwise. Of course, you can always do it for just organizational purposes as well.

share|improve this answer

It was not previously best practice because schemas were hidden prior to SQL 2005, everything was put into the dbo schema. The SQL Server Team does show it as a best practice and published an article about it: SQL Server Best Practices – Implementation of Database Object Schemas

As far as your other question regarding who should own it: dbo schema is owned by the dbo user account.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.