AlwaysOn with Standard vs. Contained Databases: Where Login Sync and Database Moves Break

What an Availability Group Actually Copies

An AG replicates the databases you add to it: log records shipped from primary to secondary, the same mechanism behind database mirroring. It does not replicate anything that lives at the instance level: server logins, SQL Server Agent jobs, linked servers, credentials, certificates outside the database's own master key. Whether that gap matters depends on where user authentication actually lives, which is the one design choice between a standard database and a contained database.

Traditional AlwaysOn: Standard Database

The database box replicates cleanly, that part AlwaysOn was built for. The logins box next to it does not exist inside the AG at all. SQL02 either already has a login called AppUser with the same SID as SQL01, because someone put it there, or it does not, and the application starts failing the moment SQL02 becomes primary.

Problem 1: Server Logins Go Stale the Moment Anyone Stops Watching

A login created on the primary after go-live does not exist on the secondaries, and never will on its own. This isn't a setup-time task, it's a standing operational gap: every new application login, every password rotation, every new service account has to be pushed to every replica for as long as the AG exists.

The failure mode is specific and easy to misdiagnose. SQL Server maps database permissions through the login's SID, not its name, so a login with the right name but the wrong SID on a secondary produces an orphaned user after failover: the application connects successfully (the login exists), then gets permission errors inside the database (the SID doesn't map). The fix is a one-liner once you know that's the problem:

ALTER USER AppUser WITH LOGIN = AppUser;

The harder part is noticing it needs to run, on the right database, on the replica that just became primary, at 3 a.m. This is covered in more depth, including a scheduled-job approach, in Syncing SQL Logins Across an AlwaysOn Availability Group.

When the Application Creates Its Own Logins

Problem 1 above assumes a DBA or a change process creates the login, which at least means someone in the room knew it happened. Plenty of applications, ERPs, trading and portfolio-management platforms such as FrontArena among them, provision their own SQL logins and database users directly as part of onboarding a new user or a new integration, through the application's own admin screen, with no DBA in that workflow at all.

That changes the shape of the problem, not just its size:

• There is no change window to catch. A one-time login copy done during go-live, or even a weekly review, catches nothing created since. The next login the application creates is out of sync on every secondary the moment it exists, with nobody aware it needs syncing until a failover exposes it.
• Only a standing, unattended sync job closes the gap, because there is no reliable point in time to run a manual one instead. This is the one case in this article where "just remember to sync after changes" was never a workable plan to begin with.
• App-created logins often carry a weaker security posture than ones a DBA provisions by hand: CHECK_POLICY = OFF is common (the application manages its own password rules and does not want SQL Server's policy rejecting its generated passwords), and permissions are occasionally broader than the integration actually needs, up to and including sysadmin, because a vendor's install guide asked for it once and nobody revisited it.

Containment does not fix this on its own. Whether a newly created principal lands as a contained database user or a classic server login is decided entirely by which statement the application issues at creation time: CREATE USER ... WITH PASSWORD for a contained user, versus the classic CREATE LOGIN followed by CREATE USER ... FOR LOGIN. An application was written for one pattern or the other; turning on CONTAINMENT = PARTIAL on the database it happens to be using does not make it switch. Most commercial, off-the-shelf applications, particularly ones that predate SQL Server 2012, use the classic pattern regardless of what the target database's containment setting is. Assume any application you did not build yourself keeps creating server-level logins whichever way the database is configured, and keep the sync job running either way.

Problem 2: Moving a Database to a New Server or a New AG

Failover keeps a database inside the same AG, on a replica that was already prepared for it. Moving a database is a different operation: consolidating instances, decommissioning old hardware, standing up a new AG in a different region, or handing a database to a different team. With a standard database, the login problem above doesn't just recur once, it becomes the entire migration checklist:

• Every login the database's users depend on has to be scripted out and recreated on the destination, including logins shared by other databases still on the source instance, which have to stay behind.
• Every user has to be re-pointed after the move, the same ALTER USER ... WITH LOGIN pattern as failover, just for every user instead of the ones that happened to drift.
• SQL Server Agent jobs, linked servers, and any process referencing the old instance name have to be found and updated by hand; none of it lives inside the database, so none of it comes along with a backup/restore or an AG add.
• None of this is visible from inside the database itself. It's tribal knowledge or a runbook, not something SQL Server tracks for you.

How a Contained Database Changes the Picture

A database with CONTAINMENT = PARTIAL can hold its own users directly: a password hash for a SQL-authenticated contained user, or a Windows SID for a Windows-authenticated one, stored inside the database instead of pointing at a server login. Because the AG ships log records for the whole database, that user catalog travels along for free.

AlwaysOn with a Contained Database

Failover no longer touches the login problem for these users at all: SQL02 already has whatever the database's contained users need, because it arrived with the last log record shipped. Moving the database to a different server or a different AG carries the same benefit, restore the database (or add it to a new AG), and its users are already there, correct SIDs and all.

Turning On Containment Doesn't Convert Existing Users

Enabling containment on a database that already has users is additive, not transformative:

ALTER DATABASE MyDb SET CONTAINMENT = PARTIAL;

This makes it possible for the database to hold contained users from now on. It does not touch a single user that already exists. Every user mapped to a server login before the change is still mapped to that exact same server login afterward, still exposed to the identical orphaned-user and SID-mismatch failure from Problem 1. Nothing about running this one statement moves anyone onto contained authentication.

To actually convert an existing SQL-authenticated user, SQL Server ships a system procedure for exactly that:

EXEC sp_migrate_user_to_contained
    @username = 'AppUser',
    @rename = 'keep_name',
    @disablelogin = 'do_not_disable_login';

@disablelogin lets the original server login keep working during a transition, in case another database or another application still depends on it, or disables it once nothing else does. This has to be run once per user, on the primary; it is a one-time migration step, not something a recurring sync job should ever do on its own.

A partially contained database is not all-or-nothing: newly onboarded users can be created as contained from day one while long-standing users stay login-mapped until someone gets around to converting them, both kinds coexisting in the same database indefinitely if that's where the migration stalls.

Moving a Contained Database to Another Domain

A contained SQL login's credential, its password hash, is a self-contained fact stored inside the database. It references nothing outside itself, which is exactly what makes it portable to any server, any AG, any domain, with no extra work. A contained Windows-authenticated user does not get the same portability, and the two look identical in sys.database_principals, which is what makes this easy to miss.

A contained Windows user's identity is still an Active Directory SID. Containment stores that SID inside the database instead of relying on a matching server login, which solves the AlwaysOn replication gap from Problem 1, but it does nothing about the SID's dependency on the domain that issued it. Move the database to a server in a different domain, or a domain outside the original AD trust (a divested subsidiary, an acquisition being separated onto its own infrastructure, a DR site in a different forest), and the stored SID stops resolving to anyone. The failure mode is the same orphaned-Windows-login problem a standard database has, just quieter: the user still exists in the catalog, containment status hasn't changed, nothing looks broken until DOMAIN\jsmith tries to connect from the new environment and there is no AD account with that SID left for SQL Server to check against.

What Containment Fixes, and What It Doesn't

Containment solves the piece of the migration checklist that was the most repetitive and the easiest to get wrong: the per-user, per-login remapping. It does not solve the rest of it. A database move still needs its own checklist for Agent jobs, linked servers, and any credential or certificate that lived at the instance level, whether or not the database itself is contained.

The Security Trade-off That Gets Skipped

Contained database authentication is off by default and has to be turned on explicitly:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

ALTER DATABASE MyDb SET CONTAINMENT = PARTIAL;

That setting is an instance-wide authentication policy change, not a per-database convenience flag. A contained user authenticates straight into a database without a server-level login in the path at all, which means server-level login auditing and server-level login triggers never see that connection the way they see everything else. Anyone with permission to create users inside a contained database can create another contained user without an instance-level administrator being involved.

sqmSQLTool Support for "Normal" AlwaysOn

Everything in this article applies whether or not a single line of PowerShell is involved. For a standard-database AG built and run with sqmSQLTool, these are the functions that carry the actual work:

Setup and topology

• New-sqmAvailabilityGroup / Add-sqmDatabaseToAG / Remove-sqmDatabaseFromAG — create an AG and add or remove databases from it.
• Invoke-sqmAlwaysOnSetup — end-to-end AG setup automation.
• Invoke-sqmSqlAlwaysOnAutoseeding — configure automatic seeding instead of manual backup/restore per replica.
• Move-sqmAlwaysOnListener, with Invoke-sqmListenerMigrationPrep / Complete-sqmListenerMigration — relocate the listener without an application connection-string change.

Operations and recovery

• Invoke-sqmFailover — a controlled failover.
• Sync-sqmAgNode — catch up a replica that has fallen behind.
• Repair-sqmAlwaysOnDatabases, with New-sqmAlwaysOnRepairJob for a standing Agent job — re-add a database that dropped out of the AG after an unplanned event, automatically.

Health and drift reporting

• Get-sqmAlwaysOnHealthReport — the redo-queue and synchronization picture in one call instead of hand-rolled DMV queries.
• Get-sqmAlwaysOnFailoverHistory — a timeline of what happened and when.
• Get-sqmAlwaysOnQueueStatus — send-queue and redo-queue depth per replica.
• Export-sqmAlwaysOnConfiguration — a point-in-time snapshot of the AG's configuration.

The login and configuration drift this article is actually about

• Sync-sqmLoginsToAlwaysOn / New-sqmAutoLoginSyncJob — the standing fix for Problem 1 and for application-managed logins.
• Compare-sqmAlwaysOnLogins — diff logins across replicas without changing anything, useful before deciding a sync run is even needed.
• Compare-sqmAlwaysOnRoles — the same diff for server role membership.
• Invoke-sqmLoginAudit — the policy/password/permission audit that catches what a sync job would otherwise faithfully replicate unexamined.

Moving across AGs, sites, or domains

• New-sqmDistributedAvailabilityGroup / Add-sqmDatabaseToDistributedAg — link two Availability Groups, sqmSQLTool's supported way of modeling "another AG" in Problem 2, including one in a different domain or datacenter.
• Test-sqmDistributedAgReadiness — pre-flight checks before creating that link.
• Invoke-sqmDistributedFailover / Get-sqmDistributedAgHealth — failing over and monitoring the distributed topology once it exists.

Practical Guidance

• Standard database, plus a scheduled sync job, when the compliance baseline restricts contained authentication, or when the database's users are shared with other databases on the same instance.
• Contained database when database portability across servers or AGs is a recurring requirement, for example frequent migrations or a multi-tenant database that moves between hosts, and the security owner has signed off on the authentication change.
• Either way, a database move between AGs still needs its own checklist for Agent jobs, linked servers, and credentials. Containment only removes the login line item from that list, not the list itself.
• If an application provisions its own logins, run the sync job unattended regardless of which database type is in play, and pair it with a scheduled login audit; containment does not change what the application itself issues at creation time.
• Before enabling containment on an existing database, plan the per-user conversion (sp_migrate_user_to_contained) as its own step. Flipping CONTAINMENT = PARTIAL does not do it for you.
• Before moving a contained database to a new domain, check whether its users are SQL- or Windows-authenticated. Only the SQL-authenticated ones are actually domain-portable.

Questions People Actually Ask