Should dbo schema be avoided?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.