MS SQL Server Login Account, DB User, and Schema Mapping (Compared with Oracle)

1. Introduction

Recently, while serving as a TA on a CI/CD deployment-management system project, I also took on some DBA work. In that process, I got to directly handle MS SQL Server's account management structure.

I had experience with Oracle DB before, but the two DBMSes differ greatly in their concepts of account (User), schema (Schema), and permission management, so I went through considerable confusion at first.

In this post, I'll organize the differences in the account·schema·permission structure that are commonly confusing when moving from Oracle to MSSQL, and also share the sysadmin-account's dbo-schema-locking problem I experienced firsthand, along with the structural solution.

2. The difference in account/schema concepts between Oracle and MSSQL

The part many Oracle-experienced people find most confusing when they first encounter SQL Server is exactly that the login account and the schema operate separately.

Oracle

User = Schema = Login account

• When you create an account, a schema with the same name is automatically created.
• If you create the SCOTT account, you immediately work in the SCOTT schema.

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

MS SQL Server

Login account (server-level account)

• An account for connecting to the SQL Server instance.
• A server-wide authentication account that logs in to the instance via ID/PW.

User account (DB-level account, after login)

• After login, a user account that belongs to a specific database.
• One Login account must be mapped 1:1 to a User account within each database.
• That is, even the same Login can have a separate User per DB.

Schema (object container)

• A container that logically separates database objects such as tables, views, and procedures.
• It's a concept separate from the User, and each User can designate a default schema (Default Schema) to use.
• Initially the default schema is set to the dbo schema. You have to change this default schema to the schema you want to use.
• While managing multiple schemas, you can fine-grain the access permissions.

-- 1) Create the server login account
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 the user in a specific DB (in MS SQL you always have to switch to using a specific db;
--    Oracle doesn't need this because schema and account are mapped 1:1)
USE SCOTT_tiger;
-- in SSMS you also write the GO command (with DBeaver you don't have to)
CREATE USER db_user FOR LOGIN SCOTT WITH DEFAULT_SCHEMA = SCOTT_tiger;
-- creates the db_user account inside the mydb database, mapped to the login account SCOTT.
-- And designates SCOTT_tiger as that db_user account's default schema.

That is, MSSQL is built as a 3-level structure (Login → User → Schema). A server-level Login account exists, and this login is mapped to a User (user account) within each database. After that, that User designates one of the multiple Schemas (object containers) in the DB as its default schema (Default Schema) to use.

Oracle, by contrast, has a 1-level structure (User = Schema = Login). That is, in Oracle the login account acts as the user and the schema at once, and there's no separate mapping concept like in MSSQL.

Therefore, clearly mapping the Login → User → Default Schema relationship, and creating a dedicated login account per schema so each uses only its schema, is the safest and most management-efficient. (In the end… Oracle's single structure is much more intuitive..)

3. The difference in Default Schema

Organizing the differences between Oracle and SQL Server regarding the Default Schema mentioned above is as follows.

Oracle

• When a user connects with their account, that account's schema is automatically set as the Default Schema.
• That is, because of the User = Schema structure, no separate setting is needed.
• For example, when you log in with the SCOTT account, you're automatically connected to the SCOTT schema.

SQL Server

• If you don't designate DEFAULT_SCHEMA when creating a User, the default value is always set to dbo.
• Therefore, to use the schema you want as the default, you must explicitly designate it as follows.

ALTER USER a_user WITH DEFAULT_SCHEMA = a_schema;

If you don't set this, when objects are created they're auto-created in the form dbo.table_name, which can cause the problem of objects piling up under the dbo schema against your intent.

4. Permission management comparison (Oracle vs SQL Server)

Both Oracle and SQL Server provide permissions and roles (Role) to control database access and operations, but they differ in structure and scope.

In Oracle, permissions are broadly divided into System permissions and Object permissions.

• System permissions are permissions that affect the entire database — for example, CREATE SESSION, CREATE TABLE, etc.
• Object permissions are access permissions on individual objects such as specific tables and views — SELECT, INSERT, UPDATE, DELETE, etc.
• It also provides Roles that bundle multiple permissions for management, so you can use predefined roles like DBA, RESOURCE, CONNECT, or create user-defined roles.

By contrast, SQL Server has a somewhat more layered permission system.

• First there are server-level permissions, which manage permissions that affect the whole database server, such as sysadmin, serveradmin, securityadmin.
• DB-level permissions apply at the database unit, such as CONNECT, CONTROL, ALTER.
• Schema/object-level permissions allow granting permissions on a specific schema or object — for example, you can designate it as follows.

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

• SQL Server also provides Roles, and through built-in roles such as db_datareader, db_datawriter, db_ddladmin, you can bundle and manage permissions.

In summary, Oracle distinguishes permissions as system-wide vs object-unit and manages them around roles, whereas SQL Server has a permission system layered into server → DB → schema/object levels, and its roles too are combined with hierarchical permission management.

5. Confusion points encountered in practice

This time, when I logged in to MSSQL with the sysadmin account, I experienced the phenomenon where, no matter how I changed the Default Schema, I always entered the dbo schema. The reason is clear. In MSSQL, the sysadmin account is designed to always map to the dbo schema, and this is a fixed behavior by security and permission-management policy.

That is, the DBA (=sysadmin) account cannot freely change its Default Schema and always starts from dbo. Therefore, in practice it's common to create and map a separate login ID per schema, apart from the sysadmin account; doing so lets you operate per-account schemas like in Oracle DB.

6. Solution: separating task accounts and schemas

Leave the DBA-privileged account for operations/management only, and to actually use a specific schema as the default, you have to create a separate login account.

(Example)

• Server-management account: sysadmin (locked to the dbo schema)
• Per-task accounts:
• a_user → uses a_schema as the default schema
• b_user → uses b_schema as the default schema

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

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

This way, you can keep the DBA account for management only and design it so each schema is used as the default through per-task User accounts.

[Overall diagram]

[Server Level]
   ├── Logins
   |     ├── sysadmin
   │     ├── a_user
   │     ├── b_user
   │     ├── c_user
   │     └── d_user
   │
[Database Level]
   ├── Users
   │     ├── dbo              ← the representative User of sysadmin or the db owner
   │     ├── a_user
   │     ├── b_user
   │     ├── c_user
   │     └── d_user
   │
   └── Schemas
         ├── dbo              ← the default schema (Default Schema)
         ├── a_user
         ├── b_user
         ├── c_user
         └── d_user

7. Summary (vs Oracle)

8. Conclusion

• In Oracle, account = schema = login
• In MSSQL, Login, User, and Schema each exist separately and need mapping
• The sysadmin (DBA) account is always mapped to the dbo schema → its Default Schema can't be changed
• Therefore, to use a per-task Default Schema, you have to create a separate Login-User account

---

📦 Migrated from the Tistory blog I used to run. Original: taehyuklee.tistory.com/29