AX / Dynamics 365 For Finance and Operations blog

Security in Azure SQL Database often starts with the principle of least privilege: a user should have access only to what they need, nothing more. A common pattern is to create a contained database user who can log in directly to a database, own a private schema, and have full control only inside it — creating, altering, and dropping objects freely, while remaining blind to everything else.

Why Use Schema-Level Isolation?

In shared databases, different applications or departments sometimes need to store their own data without touching each other’s structures. Granting everyone broad permissions like db_datareader or db_datawriter is easy but unsafe. Schema-level isolation gives each user their own sandbox while maintaining a single database for administration and cost efficiency.

Step 1 – Create the Contained User

Azure SQL uses contained users, meaning their authentication lives inside the database itself — no server-level login is required. Connect as a privileged user (for example, db_owner) and run:

--Run script inside your database (NOT in Master DB)

CREATE USER [cda] WITH PASSWORD = MyPassword123';

This creates a self-contained SQL user named cda that can authenticate directly to your target database.

Step 2 – Create and Assign the Schema

Now create a schema with the same name and assign ownership to that user:

CREATE SCHEMA [cda] AUTHORIZATION [cda];
ALTER USER [cda] WITH DEFAULT_SCHEMA = [cda];

By owning the schema, the user automatically gains control over all objects inside it.

Step 3 – Grant Database-Level Create Permissions

Schema ownership alone isn’t enough to create new tables; Azure SQL still requires a database-level permission:

GRANT CREATE TABLE TO [cda];

his allows table creation — but since cda doesn’t control any other schema, the user can create objects only within their own.

Step 4 – (Optional) Allow SSMS Login Through Master

If you want the user to log in through SQL Server Management Studio (SSMS) without specifying a database in the connection string, you can mirror the user in the master database:

--Run script inside Master DB

CREATE USER [cda] WITH PASSWORD = 'MyPassword123';

This adds a harmless “parking user” in master, with no permissions, letting SSMS connect successfully before switching context to your target db.

Step 5 – Test the Setup

Log in as cda and run:

CREATE TABLE cda.test_table (
    id INT IDENTITY(1,1) PRIMARY KEY,
    note NVARCHAR(100),
    created_at DATETIME2 DEFAULT SYSDATETIME()
);

INSERT INTO cda.test_table (note) VALUES (N'Test successful!');
SELECT * FROM cda.test_table;

If it runs, the configuration works — the user can manage everything within cda and nothing outside it.

Why This Matters

This design achieves strong isolation without creating multiple databases or complex roles. Each user has:

• Direct access via contained login
• Exclusive ownership of their schema
• Full DDL/DML freedom within it
• No visibility into other data

In short, a perfect balance between autonomy and control — ideal for multi-tenant solutions, departmental data marts, or any environment where you want to empower users without jeopardizing security.