SQL Server Schemas: dbo, Ownership and How Migration Tools Handle Them

In SQL Server a schema is a namespace inside a database, dbo is the one every database starts with, and an object created without a schema prefix lands in the caller's default schema, which is almost always dbo. That is why most SQL Server diagrams are a wall of dbo. tables. Schemas are worth using deliberately, for organization and for permissions, and every serious migration tool lets you target them; the catch is that each tool spells it differently.

Instance, database, schema, object#

SQL Server names have four parts: server.database.schema.object. A single instance hosts many databases, each database holds many schemas, and each schema holds tables, views, procedures and the rest. Within one database you can reference sales.orders and billing.invoices freely, join across them and declare foreign keys between them. Across databases you can still query with three-part names, but foreign keys cannot cross a database boundary.

This is different from MySQL and MariaDB, where "schema" is just another word for database, and closer to PostgreSQL, where schemas are namespaces inside a database and public plays the role that dbo plays here. Oracle is different again: a schema is a user. Knowing which model you are in explains a lot of migration-tool behaviour; the PostgreSQL side is covered in the PostgreSQL engine notes.

Where dbo comes from#

dbo stands for database owner. Before SQL Server 2005 a schema was tied to a user, so dbo.orders literally meant "the orders table owned by the database owner". Since then schemas and users are separate: a schema has an owner, which can be a user or a role, but objects belong to the schema and changing the owner does not rename anything. The name stuck, and every database still ships with dbo, guest, sys, INFORMATION_SCHEMA and a set of db_* schemas that mirror the fixed database roles.

Name resolution and default schemas#

When you write SELECT * FROM orders, SQL Server looks for orders in your default schema first and then in dbo. When you write CREATE TABLE orders (...), the table is created in your default schema, full stop. The default schema is a property of the database user:

CREATE USER app_user FOR LOGIN app_login WITH DEFAULT_SCHEMA = sales;
ALTER USER app_user WITH DEFAULT_SCHEMA = billing;
SELECT SCHEMA_NAME();   -- the caller's default schema

A user without an explicit default schema uses dbo. Members of the sysadmin server role always resolve to dbo regardless of the setting, which is a classic source of "it works as me but not as the service account" surprises. The rule to live by: always qualify object names with their schema, in DDL and in queries. It removes the dependency on who is running the script and makes execution plans reusable.

Creating and using schemas#

CREATE SCHEMA sales AUTHORIZATION dbo;
GO
CREATE TABLE sales.customers (
    id         bigint IDENTITY(1,1) PRIMARY KEY,
    email      nvarchar(255) NOT NULL UNIQUE,
    created_at datetime2(3) NOT NULL CONSTRAINT df_customers_created_at DEFAULT sysutcdatetime()
);
CREATE TABLE sales.orders (
    id          bigint IDENTITY(1,1) PRIMARY KEY,
    customer_id bigint NOT NULL CONSTRAINT fk_orders_customers REFERENCES sales.customers (id),
    placed_at   datetime2(3) NOT NULL
);
GRANT SELECT, INSERT, UPDATE ON SCHEMA::sales TO app_role;
ALTER SCHEMA archive TRANSFER sales.orders_2019;

CREATE SCHEMA must be the first statement in its batch, hence the GO separator when you run scripts through sqlcmd or SSMS. Permissions granted at the schema level cover every current and future object in it, which is the main practical reason to have more than one schema: give the reporting role read access to reporting and nothing else. ALTER SCHEMA ... TRANSFER moves an object between schemas without recreating it, keeping data and dependent constraints.

How migration tools target schemas#

Tools differ in two things: where they put their own history table, and how your changes pick a schema.

Two habits keep all of them honest. First, create the schema in your very first migration so that every environment has it before anything references it. Second, keep the tool's history table where the tool expects it by default unless you have a reason; moving it later means telling every environment where it went.

Cross-schema foreign keys and refactoring#

Foreign keys between schemas in the same database are ordinary foreign keys. sales.orders.customer_id can reference crm.customers.id, and a diagram will draw the arrow the same way it draws one inside a schema. What you cannot do is reference a table in another database; that needs application-level checks or triggers, and is usually a sign that the two databases want to be one.

Moving tables between schemas is cheap with ALTER SCHEMA ... TRANSFER, but code that referenced the old two-part name breaks immediately and synonyms are the usual bridge during a transition. Plan the schema layout early; the most common layouts are one schema per bounded context (sales, billing, identity) or a split by audience (app, reporting, staging).

What introspection reports#

INFORMATION_SCHEMA.TABLES and sys.tables joined to sys.schemas both give you schema-qualified names, and JDBC or ADO.NET metadata reports TABLE_SCHEM = dbo alongside the table name. Any catalog-based tool therefore sees your schemas as they are: a table created without a prefix by a sysadmin shows up under dbo, and one created by a service account with a different default schema shows up there instead. When a diagram shows a table in an unexpected schema, the migration ran under an unexpected user.

SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY s.name, t.name;

Case is a separate matter in SQL Server: names are stored as written and compared according to the database collation, which is case-insensitive by default. If you are curious how that compares with Oracle's upper-casing and PostgreSQL's lower-casing, see identifier case folding explained.

Where SchemaDrawer fits#

SchemaDrawer runs your migration files against a throw-away SQL Server (2017 through 2025) in Docker, reads every user schema of the resulting database, skipping only the server's own ones such as sys and INFORMATION_SCHEMA, and returns an SVG diagram of the tables, columns, keys and foreign keys it finds. Tables in sales and dbo alike appear, with cross-schema foreign keys drawn as arrows. It is a quick way to check that a new migration created its objects in the schema you meant, before the migration reaches a database you care about.

Key takeaways#

• A schema is a namespace inside a database; dbo is the default one and the default schema of most users.
• Unqualified CREATE TABLE goes to the caller's default schema, so always qualify names.
• Foreign keys can cross schemas but not databases.
• Every major migration tool can target schemas and relocate its history table; create the schema in migration one.