SQLTeam.com | Weblogs | Forums

Dbo owning user created database schemas


#1

Could someone help me understand why it is best practice for 'dbo' to own user-created database schemas? What are the downsides to not having 'dbo' own the user created schemas? Thanks for your help!


#2

You can have any schema you like, but whatever it is explicitly include it in front of all table references.

If you do

FROM MyTable

SQL will first look for the schema the currently connected session is using, and whether that has a Table or View, and when it doesn't find a MySessionSchema the Parser will then revert to "dbo"

So if you have either

FROM MySchema.MyTable

or

FROM dbo.MyTable

the query parser will skip the step of looking for MySessionSchema.MyTable

All our stuff uses dbo - that's the default, and we've never fiddled with specific schemas.

We have a 3rd party APP where every table is part of a single TheAPP schema ... that just means that whenever we reference a table we have to include TheAPP, so it just becomes a PITA - they might as well have used "dbo.", so I suppose my answer would be:

If you only have one Schema in your DB / APP then easiest if you stick to "dbo."


#3

FYI - if you do not include the schema then you could be creating multiple plans for each user that calls that code if each user has a different default schema. This can and does lead to cache bloat as well as issues tracking down performance issues where one user works just fine - and the other user doesn't because of a different execution plan.

Regardless of the schema - the recommendation is to schema qualify all objects.

You should also be aware that a schema is owned by a principal and that can cause permission issues based on ownership chaining. If you have MySchema owned by the principal MySchema - and reference an object in the dbo schema the user may not be able to access the data because they have to have access to both schemas.

You can define the schema to be owned by dbo - which will maintain the ownership chain.

If you do not do this - and have multiple users creating objects in their own schema but they are all on the same team - then each user has to grant permissions to their schema to someone else on their team if they want to share that data. This can be problematic for the team if they are not aware of this requirement.