SQL contained user is the shorter operational name teams use for a database-level user that can authenticate without a separate server login. The key idea is containment: the user belongs to the database and should receive database-specific permissions. In Azure SQL, contained users are common when applications use Microsoft Entra identities, managed identities, or database-scoped access for one workload. They make access easier to move with the database, but they also require discipline. Every contained user should have a reason, an owner, and a least-privilege role.
contained user, database user without login, contained SQL user, database-scoped SQL user
Difficulty
intermediate
CLI mappings
6
Last verified
2026-05-24
Microsoft Learn
Microsoft Learn explains that a contained database user is a SQL user not connected to a login in the master database. For Microsoft Entra authentication, administrators create the user in the target database from an external provider, including users, groups, service principals, or managed identities.
In Azure architecture, a SQL contained user is a database principal used in the data plane of Azure SQL. It is created and managed with T-SQL in the target database, while Azure CLI supports surrounding identity and resource validation. The principal may represent a Microsoft Entra user, group, service principal, managed identity, or SQL-authenticated database user. It interacts with database roles, schemas, permissions, connection strings, Entra administrator configuration, firewall rules, private endpoints, and auditing. It is distinct from Azure RBAC and from server-level logins, even though all three often appear in the same access review.
Why it matters
SQL contained users matter because they give teams a clean database boundary for access. Instead of granting broad server-level login rights or sharing admin credentials, an operator can create a user inside the exact database that needs access and assign only the required role. This improves portability during restore, copy, tenant migration, and disaster-recovery testing. It also fits modern Azure patterns where applications authenticate with managed identity or Microsoft Entra groups. The tradeoff is governance. If every team creates contained users casually, databases accumulate hidden access. The model works when creation, role assignment, review, and removal are scripted and audited.
⌁
Where you see it
Signals, screens, and Azure surfaces where this term usually becomes operational.
Signal 01
In the database, sys.database_principals and role membership queries show contained users, authentication type, default schema, and the roles they can use. during access reviews.
Signal 02
Azure SQL login failures, application logs, and connection-pool telemetry often expose missing contained users, wrong database names, or identities without matching database principals. during troubleshooting calls.
Signal 03
Access-review evidence combines Azure CLI identity and server output with T-SQL permission results to prove who can access a specific database. during quarterly access audits.
✦
When this becomes relevant
Specific situations where this term helps solve real Azure design, operations, migration, security, reliability, cost, or governance problems.
Give an application managed identity database access without storing a SQL password in configuration.
Represent a Microsoft Entra group as one database user so access changes happen through group membership.
Keep restored or copied databases usable without rebuilding server-login mappings for every user.
Scope contractor or partner access to a single database instead of granting broad server login rights.
Standardize tenant onboarding by scripting contained-user creation and role assignment per customer database.
◆
Real-world case studies
Different enterprise-style examples that show the term being used to hit measurable objectives.
Case study 01
Software vendor moves customer databases without orphaned users
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A software vendor regularly copied customer databases between Azure SQL servers for regional expansion. Traditional login mappings caused orphaned users and late-night support calls after each move.
🎯Business/Technical Objectives
Preserve application access when customer databases moved between logical servers.
Remove dependency on manually recreated server logins.
Use Entra group and managed identity users where possible.
Reduce post-migration authentication incidents.
✅Solution Using SQL contained user
The database team redesigned access around SQL contained users inside each customer database. Application access used managed identities represented as contained users, while support access used Entra groups mapped to database roles. Azure CLI scripts validated the destination server, database, Entra administrator, identity objects, and private endpoint before migration. After copy or restore, DBAs queried sys.database_principals and tested the application connection string against the target database. Server-level logins were no longer part of the tenant move checklist except for administrator break-glass accounts. The team also added stale contained-user review to customer offboarding.
📈Results & Business Impact
Post-migration orphaned-user incidents fell from 19 per quarter to 1 minor exception.
Average regional database move time dropped from 6 hours to 3.5 hours.
Support access became group-based, reducing individual database users by 64 percent.
Application teams stopped storing shared SQL passwords for moved tenant databases.
💡Key Takeaway for Glossary Readers
SQL contained users make database mobility less fragile when identity, roles, and connection strings are designed together.
Case study 02
Ad agency limits freelancer access to campaign data
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A digital advertising agency hired freelancers for short campaign analytics projects. Freelancers needed temporary access to one campaign database, not the entire Azure SQL server.
🎯Business/Technical Objectives
Grant temporary database-only access for approved campaign work.
Avoid giving freelancers server-level logins or administrator credentials.
Expire access automatically after the campaign closes.
Keep proof of who had access during client audits.
✅Solution Using SQL contained user
The agency created a campaign-specific Entra group and a SQL contained user for that group in the campaign analytics database. The contained user was assigned a custom role that allowed SELECT on approved views and EXECUTE on export procedures, but not direct table writes. Azure CLI captured group membership, database metadata, and server Entra administrator state before the DBA created the user. A scheduled access review removed freelancers from the Entra group at campaign close, and a T-SQL check verified the contained user no longer had active members. The process avoided changes to server-level logins and kept client audit evidence focused on one database.
📈Results & Business Impact
Freelancer onboarding time dropped from two days to under four hours.
No freelancer received server-level SQL access during the next 27 campaigns.
Access-removal misses fell from 11 per year to zero after group-based expiration.
Client audit preparation for campaign database access shrank by 70 percent.
💡Key Takeaway for Glossary Readers
Contained users are a strong fit for temporary database-specific access when membership and expiration are governed outside the database.
Case study 03
Municipal services automates permit-system access
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A city government modernized its permit system on Azure SQL. Each microservice needed narrow database permissions, but the old deployment used one shared login for inspections, payments, and notifications.
🎯Business/Technical Objectives
Assign each service its own database-contained identity.
Limit permissions to only the stored procedures each service required.
Remove the shared login from production connection strings.
Improve evidence for a public-sector security assessment.
✅Solution Using SQL contained user
The platform team enabled managed identities on the permit microservices and created SQL contained users for those identities in the permit database. Each user was mapped to a role matching the service boundary: inspections could update inspection status, payments could execute payment reconciliation procedures, and notifications could read approved message views. Azure CLI validated the managed identities, SQL database resource, Entra administrator, and firewall rules before DBAs applied the T-SQL changes. Application settings moved from stored passwords to managed identity authentication. Failed-login alerts and role-membership review queries were added to the operations workbook.
📈Results & Business Impact
The shared production SQL login was retired within two sprints.
Effective database permissions for the notification service dropped by 92 percent.
Security assessment evidence was accepted without a remediation finding for database identity.
Permit-processing incidents tied to password rotation disappeared for six months.
💡Key Takeaway for Glossary Readers
SQL contained users let teams express service boundaries directly in database permissions instead of relying on one risky shared login.
Why use Azure CLI for this?
I use Azure CLI for contained-user work because most mistakes happen before the SQL statement runs. Someone is in the wrong subscription, the wrong logical server, the wrong tenant, or the wrong managed identity. CLI lets me prove the Azure context, identity object, Entra administrator, database resource, and network path before a DBA creates or tests the contained user. It also creates a repeatable access-review trail: here is the identity, here is the database, here is the approver, and here is the private endpoint or firewall context. The database principal still requires T-SQL, but CLI keeps the cloud-side facts accurate.
CLI use cases
Verify the SQL server, database, and subscription before running T-SQL user creation.
Look up managed identity, user, group, or service principal details used by the contained user.
Confirm Microsoft Entra administrator configuration before creating external provider users.
Inspect firewall rules and private endpoints when contained-user authentication tests fail.
Export identity and database metadata for periodic access reviews and offboarding.
Before you run CLI
Confirm the intended tenant, subscription, resource group, logical server, database, and identity owner.
Know whether the contained user will represent an Entra identity, group, managed identity, service principal, or SQL password user.
Verify the target database name because contained-user connections depend on database context.
Check least-privilege database roles before adding the user to any built-in role.
Treat user creation and role grants as security-impacting even though the Azure resource itself may not change.
What output tells you
Azure resource output confirms the server and database context for the intended SQL principal.
Identity output confirms display name, object ID, client ID, or principal type before database access is granted.
Entra admin output shows whether the server can create Microsoft Entra contained users through approved administrators.
Network output helps rule out firewall or private endpoint problems before blaming SQL permissions.
Combined CLI and T-SQL evidence shows the difference between cloud ownership and actual database access.
Mapped Azure CLI commands
Contained user Azure identity and resource checks
adjacent-database-identity
az sql db show --name <database> --server <server> --resource-group <resource-group>
az sql dbdiscoverDatabases
az sql server ad-admin show --server <server> --resource-group <resource-group>
az sql server ad-admindiscoverDatabases
az ad sp show --id <application-id-or-object-id>
az ad spdiscoverDatabases
az ad group member list --group <group-name-or-object-id>
az ad group memberdiscoverDatabases
az identity show --name <managed-identity> --resource-group <resource-group>
az identitydiscoverDatabases
az sql server firewall-rule list --server <server> --resource-group <resource-group>
az sql server firewall-rulediscoverDatabases
Architecture context
Architecturally, SQL contained users are a practical implementation of least privilege at the database boundary. I use them when the application or person should not have server-wide identity. In a well-designed platform, human access goes through Entra groups, application access uses managed identities, and emergency access has a documented expiration. The contained user maps those identities to database roles and schemas. Connection strings must target the database, and monitoring must capture user creation and role changes. For multi-tenant or data-product architectures, contained users are often part of the tenant lifecycle: create on onboarding, validate during release, remove on offboarding, and review periodically.
Security
Security impact is direct. A contained user can read, write, execute, or administer data depending on granted permissions. The safest pattern is to create users for Entra groups or managed identities and map them to custom roles instead of broad built-in roles. Avoid shared SQL-contained passwords unless there is a clear operational need and a rotation process. Audit database principal creation, role membership changes, failed logins, and dormant accounts. Remember that database owners and users with ALTER ANY USER can create access inside the database. Review those permissions carefully, because containment reduces server-login dependency but does not remove insider or misconfiguration risk.
Cost
Cost impact is indirect. Contained users do not create Azure billing charges, but they can reduce expensive administration by making tenant and application access repeatable. Group-based contained users reduce one-off user work. Managed identity users reduce secret rotation effort and incident risk from leaked passwords. During migrations, avoiding broken login mappings saves DBA time and outage cost. The negative cost path is access sprawl: hundreds of unreviewed contained users create audit work, support tickets, and security risk. Periodic reviews, naming standards, and scripted provisioning keep the model economical. The biggest savings usually come from avoided manual cleanup and fewer access incidents.
Reliability
Reliability impact is indirect but operationally significant. Contained users help databases move or restore with fewer login-mapping problems, which reduces migration and recovery friction. Applications still fail if the contained user is missing, disabled, mapped to the wrong identity, placed in the wrong role, or used without the database name in the connection string. Reliable deployment treats contained-user creation as idempotent infrastructure or migration work, followed by a real connection test. For managed identities, confirm the identity still exists after resource recreation. For Entra groups, ensure membership changes are understood before critical jobs depend on access. Test before every production rollout.
Performance
Performance impact is usually not query-level. A contained user does not change indexing or execution plans. Its performance relevance is connection and operational speed. Correctly configured contained users can make onboarding, restore validation, and failover tests faster because access is scoped in the database and less dependent on external login mapping. Misconfigured users can cause repeated authentication failures, connection-pool churn, and application retries that look like performance problems. Monitor failed-login counts, retry storms, and pool exhaustion after identity changes. For high-scale SaaS environments, fast, scripted contained-user provisioning can materially improve deployment throughput and tenant readiness. That speed matters during tenant releases.
Operations
Operators handle contained users by coordinating identity owners, DBAs, application teams, and security reviewers. Common work includes creating the database principal, assigning roles, checking sys.database_principals, validating connection strings, testing from approved networks, and removing stale users. Azure CLI supports the resource and identity lookup side, while SQL tooling performs the database changes. Troubleshooting usually separates four questions: can the client reach the server, can the identity authenticate, does the contained user exist in the target database, and does it have the required permission? Runbooks should include all four, otherwise teams chase the wrong layer. Review during quarterly access reviews.
Common mistakes
Using the term contained user while still creating a traditional login-user mapping in master.
Granting db_owner to an application identity because a narrower role was not designed.
Forgetting to remove contained users when a contractor, tenant, or application component is retired.
Testing with an administrator account and assuming the contained user will have the same permissions.
Confusing Azure RBAC Contributor with database rights such as SELECT, INSERT, EXECUTE, or ALTER.