Contained Databases and Contained Users

Moving a database to another instance can leave its users stranded without matching server logins. Contained users put authentication inside a partially contained database and change that handoff.

Understand the Boundary

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.

Check Instance and Database Settings

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;

Enable Partial Containment Deliberately

Enable the server option through an approved change, then set 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.

EXEC sys.sp_configure N'contained database authentication', 1;
RECONFIGURE;

ALTER DATABASE [YourDatabase]
SET CONTAINMENT = PARTIAL;

Create Contained Users in the Database

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];

Test a Move or Restore

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.

Review Security Trade-Offs of Contained Users

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, since a database backup contains the metadata needed to restore that access 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.

List Contained Principals

sys.database_principals exposes 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;

Watch Cross-Database Dependencies

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.

Decide When Contained Users Fit

Contained 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.

Related reading on this blog: Security Considerations for Contained Databases and Orphaned Users After a Restore, and How to Fix Them.

A contained user is not a contained application, it is one authentication choice inside a larger migration plan.

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