MS SQL Server Login, User & Schema Mapping (Compared with Oracle)

1. Introduction

Recently, while serving as TA on a CI/CD deployment-management project, I also took on some DBA work and ended up dealing directly with MS SQL Server's account-management structure.

I had prior experience with Oracle DB, but the two DBMSes differ greatly in their account (User), schema, and permission concepts, which caused real confusion early on. This post organizes the account/schema/permission differences people commonly trip over when moving from Oracle to MS SQL Server, and shares the sysadmin-locked-to-dbo problem I hit, along with a structural fix.

2. Account/Schema Concept Differences: Oracle vs MS SQL Server

The thing Oracle veterans find most confusing when first meeting SQL Server is that the login account and the schema are decoupled.

Oracle — User = Schema = Login

• Creating an account automatically creates a schema of the same name.
• So creating the SCOTT account means you work in the SCOTT schema right away.

CREATE USER SCOTT IDENTIFIED BY tiger;
-- the SCOTT schema is created at the same time

MS SQL Server — Login → User → Schema (3 levels)

• Login (server level) — the account used to connect to the SQL Server instance. A server-wide authentication account that logs into the instance with an ID/PW.
• User (DB level) — a user account belonging to a specific database after login. One Login must be mapped 1:1 to a User within each database. So the same Login can have separate Users per DB.
• Schema (object container) — a container that logically groups DB objects like tables, views, and procedures. It's separate from a User; each User can set a Default Schema to use. The initial default is the dbo schema, which you should change to the schema you'll actually use.

-- 1) Create the server login
CREATE LOGIN SCOTT WITH PASSWORD = '***';

-- 2) Create the database
CREATE DATABASE SCOTT_tiger;

-- 3) Create the schema
CREATE SCHEMA vdadmin AUTHORIZATION vdadmin_user;

-- 4) Create a user in a specific DB
--    In MS SQL you must always switch (USE) to a specific DB.
--    (Oracle doesn't need this since schema and account are 1:1)
USE SCOTT_tiger;
-- In SSMS you'd also add the GO command (not needed in DBeaver)
CREATE USER db_user FOR LOGIN SCOTT WITH DEFAULT_SCHEMA = SCOTT_tiger;
-- Creates db_user inside SCOTT_tiger mapped to the SCOTT login,
-- and sets db_user's default schema to SCOTT_tiger.

In short, MS SQL Server is a 3-level structure (Login → User → Schema). A server-level Login maps to a User in each DB, and that User picks one of the DB's schemas as its Default Schema. By contrast, Oracle is a 1-level structure (User = Schema = Login) — the login account acts as the user and the schema, with no separate mapping concept.

📘 Note — even with a Default Schema set in MS SQL, you can still use objects in other schemas. Qualify them as schema.table and you can freely query tables in other schemas.

That said, when configuring external integrations or application connection accounts, it's important to set the Default Schema explicitly. Otherwise, the connection auto-maps to dbo or an unspecified default schema, which can reference unintended objects. So mapping the Login → User → Default Schema relationship clearly, and creating a dedicated login per schema to use only that schema, is the safest and most efficient approach. (In the end, Oracle's single structure is much more intuitive.)

3. The Default Schema Difference

Oracle

• When you connect with an account, that account's schema automatically becomes the Default Schema.
• Since it's User = Schema, no extra setup is needed. Log in as SCOTT and you're connected to the SCOTT schema automatically.

SQL Server

• If you don't specify DEFAULT_SCHEMA when creating a User, the default is always dbo.
• To use a different schema by default, you must specify it explicitly.

ALTER USER a_user WITH DEFAULT_SCHEMA = a_schema;

Without this, objects are auto-created as dbo.table_name, piling up under dbo against your intent.

4. Permission Management: Oracle vs SQL Server

Both DBMSes provide permissions and roles for access/operation control, but the structure and scope differ.

Oracle — permissions split broadly into System and Object permissions.

• System permissions — affect the whole DB (e.g., CREATE SESSION, CREATE TABLE).
• Object permissions — access to specific objects like tables/views (SELECT, INSERT, UPDATE, DELETE).
• It provides Roles to bundle permissions — predefined ones like DBA, RESOURCE, CONNECT, or your own custom roles.

SQL Server — a more layered permission system.

• Server-level permissions — sysadmin, serveradmin, securityadmin, etc., affecting the whole server.
• DB-level permissions — CONNECT, CONTROL, ALTER, etc., per database.
• Schema/object-level permissions — grants on a specific schema or object.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::X TO user;

• SQL Server also provides built-in Roles like db_datareader, db_datawriter, db_ddladmin to bundle permissions.

In short, Oracle splits permissions into whole-system vs object level and manages them role-centrically, while SQL Server has a hierarchy of Server → DB → Schema/Object with roles bound to that hierarchy.

5. A Confusion Point in Practice

Logging into MS SQL with the sysadmin account, I hit a case where it always landed on the dbo schema no matter how I changed the Default Schema. The reason is clear: in MS SQL the sysadmin account is designed to always map to dbo, a fixed behavior for security/permission policy.

So a DBA (sysadmin) account can't freely change its Default Schema and always starts at dbo. In practice, you separately create per-schema logins and map them apart from sysadmin, which lets you operate per-account schemas like Oracle.

6. The Fix — Separating Task Accounts and Schemas

Keep DBA-privileged accounts for ops/management only, and create separate logins to use a specific schema by default.

• Server admin account — sysadmin (locked to dbo)
• Per-task accounts — a_user → a_schema, b_user → b_schema as default schemas

-- Task account A
CREATE LOGIN a_user WITH PASSWORD = '***';
USE mydb;
CREATE USER a_user FOR LOGIN a_user WITH DEFAULT_SCHEMA = a_schema;

-- Task account B
CREATE LOGIN b_user WITH PASSWORD = '***';
USE mydb;
CREATE USER b_user FOR LOGIN b_user WITH DEFAULT_SCHEMA = b_schema;

This keeps the DBA account for management only while per-task User accounts use their own schema by default. The overall layout:

[Server Level]
   ├── Logins
   │     ├── sysadmin
   │     ├── a_user
   │     ├── b_user
   │     ├── c_user
   │     └── d_user
   │
[Database Level]
   ├── Users
   │     ├── dbo              ← representative User for sysadmin or db owner
   │     ├── a_user
   │     ├── b_user
   │     ├── c_user
   │     └── d_user
   │
   └── Schemas
         ├── dbo              ← default schema
         ├── a_user
         ├── b_user
         ├── c_user
         └── d_user

7. Summary (vs Oracle)

8. Conclusion

• In Oracle, account = schema = login.
• In MS SQL Server, Login, User, and Schema each exist separately and need mapping.
• The sysadmin (DBA) account is always mapped to dbo and can't change its Default Schema.
• So to use per-task Default Schemas, you must create separate Login–User accounts.

---

📦 Migrated from my own Korean blog (my own writing). Original: taehyuklee.tistory.com/29