DEV Community

Cover image for What is dbo in SQL Server?
Retiago Drago
Retiago Drago

Posted on • Updated on

What is dbo in SQL Server?

I used to learn about SQL, especially MySQL. Then now my job requires me to understand Microsoft SQL Server (mssql). So I make these notes to guide me and others who want to transition to mssql. I usually summarized what I found and understood so far from browsing the internet.

My takeaways:

  • dbo stands for DataBase Owner.

  • We can't remove privileges from an object owner and we can't drop users from a database if they own objects in it.

  • schema is a named container for database objects, which allows us to group objects into separate namespaces. The schema is the database object that owns the table. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.
    AdventureWorks database

  • The four-part naming syntax for referring to objects specifies the schema name.

Server.Database.DatabaseSchema.DatabaseObject
Enter fullscreen mode Exit fullscreen mode
  • Schemas can be owned by any database principal and a single principal can own multiple schemas. Principals are entities that can request SQL Server resources.

  • Schemas that contain objects cannot be dropped. The following schemas cannot be dropped: dbo, guest, sys, INFORMATION_SCHEMA.

  • The sys and INFORMATION_SCHEMA schemas are reserved for system objects. We cannot create objects in these schemas and we cannot drop them.

  • The dbo schema is the default schema of every database for all users. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account.
    user-schema-object

  • For example, the name of a table called orders owned by dbo is dbo.orders. If the table’s ownership is transferred to user abc, the table will now be named abc.orders.

  • Users who are assigned the dbo as default schema don't inherit the permissions of the dbo user account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.

  • the dbo user account is not the same as the db_owner fixed database role and the db_owner fixed database role is not the same as the user account that is recorded as the owner of the database.

  • The default schema for a user is solely used for object-reference in case the user omits the schema when querying objects. Users in the database will be able to access any object owned by dbo without specifying the owner as long as the user has appropriate permission.

References:

[1] ownership-and-user-schema-separation
[2] creating-database-objects
[3] understanding-the-difference-between-owners-and-schemas-in-sql-server
[4] owners-and-schemas
[5] principals

Discussion (0)