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?
-
Some consultant said that it is good practice to avoid dbo schema and always create user defined schemas and assign objets into these schemas.– jraraDec 7, 2011 at 19:42
-
I found this statement also from Alexander Kuznetsov in the comments of this blog post:– jraraDec 7, 2011 at 19:50
-
Yes, avoid it now. dba.stackexchange.com/a/4109/630 and dba.stackexchange.com/q/8511/630 and stackoverflow.com/q/6502222/27535 @JNK: FYI for you too– gbnDec 7, 2011 at 23:18
Add a comment
|
4 Answers
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.
answered Dec 7, 2011 at 20:07
Eric Humphrey - lotsahelpEric Humphrey - lotsahelp
5,0031 gold badge23 silver badges28 bronze badges
-
1100% 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.– pimAug 2, 2018 at 14:03
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.– BradCDec 7, 2011 at 21:49
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.