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?
Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.Visit Stack Exchange
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 only takes a minute to sign up.Sign up to join this community
When it comes to the dbo schema:
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.
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.
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.
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.