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.
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.
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.
|
1 2 3 4 5 6 7 8 9 |
USE master GO CREATE LOGIN [Developer1] WITH PASSWORD = '', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF; GO USE AdventureWorks2019 GO CREATE USER [Developer1] FOR LOGIN [Developer1]; ALTER ROLE [db_ddladmin] ADD MEMBER [Developer1]; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE master GO /*Impersonate Developer1*/ EXECUTE AS LOGIN = 'Developer1'; USE [AdventureWorks2019] GO /*Create some tables*/ CREATE TABLE [Dev1Tbl] (ID INT); /*This will end up in the user's default schema*/ CREATE TABLE [HumanResources].[Dev1Tbl] (ID INT); CREATE TABLE [Sales].[Dev1Tbl] (ID INT); /*Check table info*/ SELECT [name], [object_id], [principal_id], [schema_id], SCHEMA_NAME([schema_id]) AS [SchemaName], [type_desc] FROM sys.all_objects WHERE [name] = N'Dev1Tbl'; |
I also check if the Developer1 user creating any tables somehow ended up creating a schema with the same name as the user.
|
1 |
SELECT * FROM sys.schemas; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE master GO /*Revert to sa's security context*/ REVERT; GO USE [AdventureWorks2019] GO /*Drop the database user*/ DROP USER [Developer1]; GO /*Check if the user is gone*/ SELECT [name] FROM sys.database_principals WHERE [name] = N'Developer1'; /*Check table info*/ SELECT [name], [object_id], [principal_id], [schema_id], SCHEMA_NAME([schema_id]) AS [SchemaName], [type_desc] FROM sys.all_objects WHERE [name] = N'Dev1Tbl'; |
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.
|
1 2 3 4 5 6 |
USE master GO CREATE LOGIN [DatabaseCreator] WITH PASSWORD = '', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF; GO GRANT CREATE ANY DATABASE TO [DatabaseCreator]; GO |
I then impersonate the newly created login, create a new database and check its ownership.
|
1 2 3 4 5 6 7 8 9 10 11 |
EXECUTE AS LOGIN = 'DatabaseCreator'; /*Validate that I'm running as the DatabaseCreator login*/ SELECT SUSER_NAME() AS [LoginName]; /*Create the database*/ CREATE DATABASE TestDB; GO /*Check newly created database*/ SELECT [name] AS [DatabaseName], SUSER_SNAME([owner_sid]) AS [DatabaseOwner] /*Get the login name of the database owner*/ FROM sys.databases WHERE [name] = N'TestDB'; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE TestDB GO /*As what user is DatabaseCreator interacting with the database*/ SELECT SUSER_NAME() AS [LoginName], USER_NAME() AS [DatabaseUserName]; /*Which login is dbo mapped to*/ SELECT [name], [principal_id], SUSER_SNAME([sid]) AS [LoginName], [type_desc], [default_schema_name], [authentication_type_desc] FROM sys.database_principals WHERE [principal_id] = 1; |
Now let’s check why is dbo able to do anything in the database.
|
1 2 3 4 5 6 7 8 |
/*Check role membership*/ SELECT u.[name] AS [UserName], r.[name] AS [RoleName] FROM sys.database_role_members AS rm INNER JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id INNER JOIN sys.database_principals AS u ON u.principal_id = rm.member_principal_id; |
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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/*Switch to master database*/ USE master GO /*Revert to sa's security context instead of DatabaseCreator*/ REVERT; GO /*Set sa as the database owner*/ ALTER AUTHORIZATION ON DATABASE::[TestDB] TO [sa]; /*Check database ownership*/ SELECT [name] AS [DatabaseName], SUSER_SNAME([owner_sid]) AS [DatabaseOwner] /*Get the login name of the database owner*/ FROM sys.databases WHERE [name] = N'TestDB'; |
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.
|
1 2 3 4 5 6 |
/*Impersonate the DatabaseCreator login again*/ EXECUTE AS LOGIN = 'DatabaseCreator'; GO /*Try to switch to TestDB*/ USE TestDB GO |
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.
|
1 2 3 4 5 6 7 8 9 10 |
/*Revert to sa's security context instead of DatabaseCreator*/ REVERT; GO /*Swith to TestDB*/ USE TestDB GO /*Create the user*/ CREATE USER [DatabaseCreator] FOR LOGIN [DatabaseCreator]; /*Add user to the db_owner role*/ ALTER ROLE [db_owner] ADD MEMBER [DatabaseCreator]; |
And then I check user to login mapping and role membership.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/*Check database user to login mapping*/ SELECT [name], [principal_id], SUSER_SNAME([sid]) AS [LoginName], [type_desc], [default_schema_name], [authentication_type_desc] FROM sys.database_principals WHERE [principal_id] = 1 OR [name] = N'DatabaseCreator'; /*Check role membership*/ SELECT u.[name] AS [UserName], r.[name] AS [RoleName] FROM sys.database_role_members AS rm INNER JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id INNER JOIN sys.database_principals AS u ON u.principal_id = rm.member_principal_id; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/*Impersonate a login that's also a member of the sysadmin fixed-server role*/ EXECUTE AS LOGIN = 'TESTVM\Vlad'; USE TestDB GO /*As what user am I interacting with the database now*/ SELECT SUSER_NAME() AS [LoginName], USER_NAME() AS [DatabaseUserName]; /*Does this mean that dbo is no longer mapped to sa?*/ SELECT [name], [principal_id], SUSER_SNAME([sid]) AS [LoginName], [type_desc], [default_schema_name], [authentication_type_desc] FROM sys.database_principals WHERE [principal_id] = 1; |
Conclusion
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.
