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?
up vote 19 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.

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.

  • 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.

  • 100% behind this approach. I often leave the "core" tables in the dbo schema for simplicity and begin adding when needed. Typically the first separate schema I add is "Stage" or "Staging", to group my staging tables separately. Another example would be adding data from an external source, say Twitter. Instead of prefixing all the tables (i.e. TwitterAccount, TwitterStatus etc.) I'll create a "Twitter" schema. – pimbrouwers Aug 2 at 14:03

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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