Last updated: August 29, 2026.
A contained database user authenticates at the database level and does not depend on a server login SID. It can simplify portability, but the server and client connection must support the model.
Enable and create a contained user
EXEC sys.sp_configure N'contained database authentication', 1;
RECONFIGURE;
ALTER DATABASE YourDatabase SET CONTAINMENT = PARTIAL;
GO
USE YourDatabase;
CREATE USER AppUser WITH PASSWORD = 'Use-a-secret-from-your-vault';
ALTER ROLE db_datareader ADD MEMBER AppUser;Use least privilege
- Connect with an explicit database name.
- Grant only required database permissions.
- Keep passwords outside deployment scripts.
- Audit containment and authentication requirements before migration.
Use containment for a defined portability need
Contained users reduce dependence on server login SIDs, but they change how clients authenticate and how administrators manage credentials. Enable partial containment only after reviewing server policy and connection behavior.
Connect with an explicit database name and test backup restore to a compatible instance. Review database roles and keep contained-user passwords in the same secret-management process as other application credentials.
- Grant least privilege.
- Audit contained authentication settings.
- Document client connection requirements.
Run administrative statements first in a controlled environment and record the current configuration. Keep a rollback or restore path, use least privilege, and verify the result through the same client path used by the application.
Continue with orphaned users, login SID transfer, and restore a backup.
Practical implementation check
Before changing production, record the server version, relevant configuration, current object state, and a tested recovery path. Run the diagnostic query with an account that has only the permissions it needs. Apply the smallest change that addresses the evidence, then repeat the original check and monitor application behavior instead of assuming a successful statement completed the task.
Record the final setting or object state in the deployment notes, including why it was chosen. That evidence makes later capacity reviews, migrations, and incident response substantially faster.
Reference: Microsoft contained database users guidance.
