Contained Database Users: Logins That Move With the Database

A restored database can arrive with users that no longer match server logins. Contained database users carry database-level authentication with the restore. They still need an enabled instance setting and a clear credential owner.

Where Contained Database Users Draw the Line

With contained database users, authentication travels at database scope. A traditional SQL login lives at the server level and maps to a database user, usually through a security identifier. A contained database user with password is authenticated within the database.

That can simplify a move or restore because the user travels with the database. It also changes where credentials are managed and audited.

I explain the boundary before showing CREATE USER. Containment is not a magic portability switch for every dependency. Jobs, linked servers, certificates, server permissions, and application connection settings can still live outside the database.

Ask what must move with the application. The user account is only one piece of that list.

Read the Instance Option and Containment Setting

The instance must allow contained database authentication, and the database must use partial containment. Both changes deserve security review. Check current settings before planning a migration.

A configuration value of zero means the instance setting is off. A database containment value of NONE means that database has not enabled partial containment.

The queries below are read-only. They show whether the environment is ready for contained users. I check these on source and target instances so a restore does not introduce an unexpected authentication failure.

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = N'contained database authentication';
SELECT name, containment_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Switch One Database to Partial Containment

The instance option contained database authentication must be enabled first. That is a server-level change for the administrator, and this demo does not make it. The block below sets only the database to PARTIAL. Test the application and security tooling afterward.

Partial containment still allows many server-level dependencies. It does not seal a database into a self-contained package. Document why this database needs the feature and who owns the contained credentials.

I do not enable the instance setting merely because one restore failed. First determine whether the application should instead have a managed server login. The choice affects credential rotation, auditing, and support.

Pick the model that matches your team's security process. A movable database is useful only when its access model remains understandable after it moves.

ALTER DATABASE [YourDatabase]
SET CONTAINMENT = PARTIAL;

Create Contained Database Users With a Password

Inside a partially contained database, CREATE USER with a password creates a database-authenticated user. Grant only the role or permissions the application needs. Do not use db_owner for convenience.

A contained user can connect by specifying the database in the connection string. Test that exact path from the application environment.

The sample uses a placeholder password and database. Replace it with a strong secret managed under your credential policy. Never paste a real secret into a shared script or incident ticket. Rotate it through an approved process and confirm that all application instances receive the update.

USE [YourDatabase];
CREATE USER [AppContainedUser]
WITH PASSWORD = 'ReplaceWithAStrongSecret';
GRANT SELECT ON SCHEMA::dbo TO [AppContainedUser];

Rehearse the Restore on a Second Instance

Restore a copy on a target instance with the server option enabled. Connect using the contained user and specify the database. Verify both the allowed operation and a denied operation.

Test jobs, cross-database queries, and any application feature that reaches outside the database. A successful login alone is not a complete migration test.

I have seen teams celebrate the first connection and discover the missing server-level job later. Keep a dependency checklist. Record certificates, credentials, linked servers, SQL Agent jobs, and endpoint settings separately.

Contained authentication solves the login mapping problem for that user. It does not transport the whole server.

Weigh the Credential Ownership Trade-Offs

Credentials stored at the database level change the administrative boundary. Anyone with sufficient database control can affect contained users. Review who can create users and how password rotation is tracked.

Include contained users in access reviews and backup handling. The backup carries metadata used to restore that authentication model.

I ask the security owner how they will inventory these accounts. A server-level login report will miss them. Build a database-level query and include it in quarterly review.

Portability should not turn into invisibility. The authentication method needs the same ownership and removal process as any other application credential.

Inventory Contained Database Users Per Database

The sys.database_principals view exposes the authentication type within a database. Use the query in each target database to find database-authenticated users. Distinguish these from users mapped to instance logins and Windows identities. A name can look familiar while the authentication model differs, so read the type column.

I keep this list beside the server login inventory. That makes a migration plan and access review complete. The query is a snapshot; it does not tell you when the user last connected or whether it remains needed. Confirm use with application owners before changing access.

SELECT name,
       type_desc,
       authentication_type_desc,
       create_date,
       modify_date
FROM sys.database_principals
WHERE authentication_type_desc = N'DATABASE'
ORDER BY name;

Trace Work That Reaches Other Databases

A contained user belongs to one database. Cross-database work can still require mappings, permissions, or redesign. Test three-part-name queries, synonyms, and procedures that reach outside the contained database.

Partial containment allows behavior that depends on the instance, so list those dependencies explicitly. The word contained describes a goal and a feature boundary, not a guarantee that every query stays inside it.

I use the migration rehearsal to expose these assumptions. If a cross-database path fails on the target, document the required access or change the application design. Do not grant broad server rights simply to make the test pass. A narrow, understood dependency is easier to support.

Choose Containment Only for a Clear Boundary

Contained database users work well when the database needs to move and the team can manage credentials at database scope. They can also fit an application that has a clear database boundary. They are a poor shortcut when the application depends heavily on server objects or when no one owns password rotation. Choose deliberately and test the complete workload.

Would a future restore team know which user connects and how its secret is rotated? If not, finish that documentation before relying on containment. The feature reduces one common migration problem. Good operations still need a full map of what lives outside the database.

Specify Initial Catalog or the database name in the connection dialog. Without it, SQL Server tries the name as a server login and the connection fails. Test the exact application connection and a denied operation.

Portability is helpful. A password that travels without an owner becomes a particularly well-traveled problem. Keep the account inventory and rotation procedure with the restore plan.

Related reading on this blog: Difference Between Login Vs User: Security Concepts and Empty Database Authentication Cache with DBCC FLUSHAUTHCACHE.

A contained user is not a portable application, it is authentication managed inside one database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.