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's 100% free, no registration required.

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 '11 at 19:42

1

I won't post this as an answer because I can't provide a complete answer, but that sounds like bunk to me. I always thought dbo was a normal schema choice since it's independent of the individual users on the DB.
–
JNK♦Dec 7 '11 at 19:45

I found this statement also from Alexander Kuznetsov in the comments of this blog post:
–
jraraDec 7 '11 at 19:50

1

Well, yeah, that was also the reason I asked this question here.
–
jraraDec 7 '11 at 19:54

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.

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.