Should SQL server dbo schema be avoided?
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?
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.trainingsAs 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.
SQL Server DBO: In SQL Server, the dbo or Database Owner is a server-level principal that has full access to the owned database. Microsoft's best practices recommend creating a discrete user, either an Active Directory domain user or group, or a SQL Server Authentication user, to use as the database owner.