Home / Guides / Sync SQL Server logins and Agent jobs

Sync SQL Server logins and Agent jobs across availability group replicas

An availability group copies your databases to the other replicas. It does not copy the logins, the SQL Server Agent jobs or the server-level settings that the databases depend on. That gap is why a failover that worked in the test can leave real users unable to sign in.

What does not travel with an availability group

Microsoft's guidance for availability groups says the logins and jobs must be reproduced on every instance of SQL Server that hosts a replica. Beyond those two, the same page lists server configuration settings, credentials, encrypted data, permissions, replication settings, Service Broker applications and server-level triggers as things you may need to recreate. In practice the ones that bite are:

• Logins. A database user whose login is missing or has a different SID on the new primary is orphaned and cannot sign in.
• SQL Agent jobs and operators. A job that exists only on the old primary simply stops running after the failover.
• Linked servers. Queries that use one fail with an error on the replica that lacks it.
• Server configuration. Settings such as memory and parallelism that differ between replicas change behavior after a failover.

Why the SID matters

A database user is tied to a login by the login's SID, not its name. Domain accounts have the same SID on every server, so a Windows login for a domain account only needs to exist. SQL Server logins and local Windows accounts get a new SID wherever they are created. If you create app_user by hand on the second replica, it has the right name and the wrong SID, and the database user on that replica is orphaned. Microsoft's fix is to transfer the login with its original SID and password hash instead of creating it fresh.

Contained databases avoid this for contained users, because the user and its password live inside the database. Most existing databases are not contained.

Find the gaps with three queries

1. Orphaned users in a database

Run in each user database on each replica (this is the query Microsoft documents for SQL Server authentication users):

SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.sid = sp.sid
WHERE sp.sid IS NULL
    AND dp.authentication_type_desc = 'INSTANCE';

2. Logins: compare names and SIDs between replicas

Run on every replica and compare the results. The row to look for is the same name with a different sid:

USE [master];
SELECT name, type_desc, CONVERT(varchar(100), sid, 1) AS sid, is_disabled
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')
  AND name NOT LIKE '##%'
  AND name NOT LIKE 'NT SERVICE\%'
  AND name NOT LIKE 'NT AUTHORITY\%'
ORDER BY name;

3. Agent jobs

Run on every replica and compare. A name that appears on one replica only is a job that will not run after a failover:

USE [msdb];
SELECT name, enabled
FROM dbo.sysjobs
ORDER BY name;

Fix it: recreate the login with its SID and hash

This generates one statement per SQL login, to run on the other replicas. Treat the output like a password file, because it contains password hashes. Windows logins for domain accounts do not need this; CREATE LOGIN [DOMAIN\name] FROM WINDOWS is enough.

USE [master];
SELECT 'CREATE LOGIN ' + QUOTENAME(name)
     + ' WITH PASSWORD = ' + CONVERT(varchar(max), password_hash, 1) + ' HASHED'
     + ', SID = ' + CONVERT(varchar(max), sid, 1) + ';' AS create_login
FROM sys.sql_logins
WHERE name NOT LIKE '##%'
  AND name <> 'sa';

Microsoft's older article for the same job is KB 918992, which uses the sp_help_revlogin script. The statement above does the same thing with built-in syntax. Run it on the current primary and review the result before you run it anywhere else.

Jobs need more than a copy

Copying a job to the other replicas is the easy half. Microsoft's guidance is to add logic at the start of each relevant job so that it only does its work when the local replica is the primary for the database, and to make backup jobs check whether the local replica is the preferred backup replica (sys.fn_hadr_backup_is_preferred_replica). Replicas can also differ in drive letters, so a job that works on one may need small changes on another.

Three ways to keep replicas in step

If you are comfortable in PowerShell and want to automate the whole thing, dbatools is a strong, free choice and widely used by DBAs. If you want to see a drift report, choose what to do about each difference, and review a plan before anything changes, that is what SQLParity is for.

Questions

Related

• SQLParity: keep your SQL Server instances the same
• Remote SQL Server DBA services
• All guides

Sources (Microsoft Learn): Manage logins and jobs for availability group databases, Troubleshoot orphaned users, Failover and failover modes.