The dbo schema and user pair tend to be a source of confusion, especially for people who come from an Oracle background and are used to schema being synonymous wh user. So what is dbo, anyway?

dbo: the schema

dbo is one of the 4 built-in schemas that exist in every SQL Server database and cannot be dropped.
By default, all database users will have dbo as their default schema, and due to that default schema mapping all the user-created objects will be created in the same schema even if it’s not prepended to the object name.
There are also 9 built-in schemas that have the same names as the built-in database roles. These only exist for backwards compatibility purposes and can safely be dropped.

Built-in schemas

So what is a schema?

A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks2019 sample database contains the schemas Production, Sales, Purchasing, Person, and HumanResources.

SSMS displayng the nont-built-in schemas found in AdventureWorks2019
Non-built-in schemas in AdventureWorks2019

In SQL Server, schemas allow for ownership separation from database users. So, if a database user creates an object, that user can be later dropped without getting any errors related to object ownership, as long as said user doesn’t own a schema.
This means that the schema is the database object’s owner, regardless of which user created said object.

Schemas can also facilitate easier and more granular object permission management.
Using AdventureWorks2019 as an example, we can create roles specific to each department, i.e. HR, grant that role SELECT,UPDATE,INSERT,DELETE, and EXECUTE permissions on the HumanResources schema only, and any HR person who might need access to the database can have their user added to the HR role.

Demo time

First, I create a new SQL login, create a user for that login in the AdventureWorks2019 database and add it to the db_ddladmin role.

I then impersonate Developer1, create a few tables in different schemas of the AdventureWorks2019 database, and query the sys.all_objects catalog view to see if the tables are tied in any way to the user.

I also check if the Developer1 user creating any tables somehow ended up creating a schema with the same name as the user.

Since principal_id is NULL, there is no ownership relation between the user and the created tables.
So I can just drop the Developer1 user without affecting the 3 tables.

The user was dropped without any issues, and the tables are still there. Unlike Oracle where the tables need to be dropped or moved to another schema in order to drop the user.

dbo: the user

Here’s where things might get a bit confusing.
dbo also acts as a database user, it’s the database principal that owns the database and it’s an acronym for DataBase Owner. It also becomes the fallback user for any login that is able to access the database but does not have a dedicated database-level user mapped to it.

When creating a database, the login that creates the database is automatically set as its owner and mapped to the dbo user.

If dbo is the database owner, then what is db_owner?

db_owner is a fixed-database role, it grants its members permission to do pretty much anything at the database level, including dropping said database, but db_owner role membership does not imply actual database ownership.

Demo time

I first create a login and grant permission to create any database.

I then impersonate the newly created login, create a new database and check its ownership.

Database ownership information can also be seen in SSMS via the database properties menu (right click on the database -> Properties).

Switch the current database to TestDB and see which user does the DatabaseCreator login use to interact with the database, as well as check to which login is dbo mapped.

SSMS Results for the above query:
First result set:
LoginName	DatabaseUserName
DatabaseCreator	dbo

Second result set:
name	principal_id	LoginName	type_desc	default_schema_name	authentication_type_desc
dbo	1	DatabaseCreator	SQL_USER	dbo	INSTANCE

Now let’s check why is dbo able to do anything in the database.

dbo is a member of the db_owner database role

So far, it’s pretty clear how dbo, the database user, behaves, how a login can end up mapped to it and why it has the permissions to do what it does.

Now what happens if sa is set as the database owner?

A side-effect of this is that the DatabaseCreator login no longer has access to the TestDB database since it’s no longer mapped to a database user.

Msg 916, Level 14, State 2, Line 56
The server principal "DatabaseCreator" is not able to access the database "TestDB" under the current security context.

To remediate this and give DatabaseCreator access to TestDB without setting that login as the owner, I create a database user mapped to that login and add it to the appropriate role.

And then I check user to login mapping and role membership.

As which user would a login interact with the database if that login is a member of the sysadmin role, but doesn’t have a database user mapped to it?

To demo how dbo can act as a fallback for logins not mapped to a user, I’ll impersonate another login that is a member of the sysadmin fixed-server role.

SSMS result for above query
First result set:
name	principal_id	LoginName	type_desc	default_schema_name	authentication_type_desc
dbo	1	sa	SQL_USER	dbo	INSTANCE
DatabaseCreator	5	DatabaseCreator	SQL_USER	dbo	INSTANCE

Second result set:
UserName	RoleName
dbo	db_owner
DatabaseCreator	db_owner


We’ve seen dbo’s uses and behavior, how and why it grants the login mapped to it full control over the database.
We’ve also seen that, in SQL Server, object ownership isn’t user-based like in Oracle’s case, and that users and schemas are two different entities. And a database user does not automatically create a schema with the same name as itself when creating tables.