What is dbo in SQL Server?
Tags: sql, beginners, mssql, sqlserver
Published: 2021-11-18 05:10:56

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ll2th3ma78aog2i8ftq7.png)

- The __four-part naming syntax__ for referring to objects specifies the __schema__ name.
```SQL
Server.Database.DatabaseSchema.DatabaseObject
```

- 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cbik4mtceibu18xfov6b.png)

- 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](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver15)
[2] [creating-database-objects](https://docs.microsoft.com/en-us/sql/t-sql/lesson-1-creating-database-objects?view=sql-server-ver15)
[3] [understanding-the-difference-between-owners-and-schemas-in-sql-server](https://www.sqlteam.com/articles/understanding-the-difference-between-owners-and-schemas-in-sql-server)
[4] [owners-and-schemas](https://morecoding.wordpress.com/2012/06/12/owners-and-schemas/)
[5] [principals](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15)

[Let's be friend & support me👋](https://beacons.ai/ranggakd)