SQL SERVER – AlwaysOn Availability Groups: Script to Sync Logins Between Replicas?

An availability group copies your databases and nothing else. Logins live outside the database, so they do not travel. Failover works perfectly, the application cannot log in, and everybody looks at the wrong thing for an hour. Here is how to sync logins properly, and why copying the password is only half the job.

Why Failover Breaks Logins

There are two objects and people mix them up. A login sits at server level and lets you connect. A user sits inside a database and decides what you can do.

The availability group ships the database, so the user goes across. The login does not. It was never in the database. After failover the user points at a login that does not exist on the new primary. That is an orphaned user. It shows up as a login failure with state 38, or a permissions error that makes no sense.

The SID Is the Part People Miss

Creating a login with the same name on the secondary is not enough. The user inside the database is matched to the login by its security identifier, not by its name.

SELECT name, type_desc, is_disabled,
       CONVERT(varchar(100), sid, 1) AS sid_hex,
       default_database_name
FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE '##%'
ORDER BY name;

A Windows login carries the domain SID, so it is the same on every server by definition. Those look after themselves.

A SQL login gets a fresh random SID from whichever instance created it. Create AppUser on two servers separately and you get two different SIDs. That is one broken failover. Create it on the secondary with the SID from the primary instead.

Reading the Password Without Knowing It

You do not need the password. You need the hash, and SQL Server will give it to you.

SELECT name,
       CONVERT(varchar(300), LOGINPROPERTY(name, 'PasswordHash'), 1) AS pwd_hash,
       CONVERT(varchar(100), sid, 1) AS sid_hex,
       is_policy_checked, is_expiration_checked, default_database_name
FROM sys.server_principals
WHERE type = 'S' AND name NOT LIKE '##%' AND name <> 'sa';

A Windows login returns NULL for the hash, which is correct. There is no password held here for it.

Creating It on the Secondary

CREATE LOGIN AppUser
WITH PASSWORD = <the hash you read above> HASHED,
     SID      = <the sid you read above>,
     DEFAULT_DATABASE = [master],
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = OFF;

HASHED tells SQL Server the value is already a hash, not a password to hash again. SID forces the identifier to match. Get both right and failover stops orphaning anybody.

For a Windows login it is much simpler, because the SID comes from the domain:

CREATE LOGIN [DOMAIN\AppService] FROM WINDOWS WITH DEFAULT_DATABASE = [master];

What Else Has to Travel

The login alone is not the whole story. These also live outside the database and are forgotten every time.

Server role membership. A login that was in a server role on the primary is not in it on the secondary.

SELECT r.name AS role_name, m.name AS member_name
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
WHERE m.name NOT LIKE '##%'
ORDER BY r.name, m.name;

Server level permissions such as VIEW SERVER STATE, which monitoring accounts need. Credentials and proxies, if agent jobs use them. Linked servers, because those are instance objects too. And agent jobs, which live in msdb and do not fail over at all.

The Test That Proves It Worked

Do not wait for a real failover to find out. Run this on the secondary against a readable copy, or on the primary after a planned failover:

USE Sales;

SELECT dp.name AS database_user, dp.type_desc, sp.name AS server_login
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.type IN ('S','U','G')
  AND dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')
ORDER BY dp.name;

Every row should have a server_login. A NULL is an orphaned user waiting to ruin a failover. That query belongs in your monthly health check.

A Better Answer Where You Can Use It

Contained database users sidestep this entirely. The user and its password live inside the database, so they fail over with it and there is no login to sync.

They do not fit everything. You give up server roles and anything reaching across databases. Where an application touches one database only, they remove a whole category of surprise.

Keep It Running

Syncing once is not a fix, it is a snapshot. Somebody will add a login next month and forget the secondary.

Put the comparison in a scheduled job that reports a difference rather than fixing it. A job that quietly creates logins will eventually create one you did not want.

A login is not part of your database, it is the door the database assumes is already there.

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