LLI Wiki — My Digital Knowledge Base

Recreate a SQL login with the same SID on Always On replicas

Generate a CREATE LOGIN with hashed password and identical SID to avoid orphaned users after an availability group failover.

In an Always On availability group, databases are replicated but logins are not, because they live in master. If an application login does not have the same SID on every replica, the database user becomes orphaned after a failover. This article shows how to recreate a SQL login on a secondary replica with the same SID and the same (hashed) password.

Generate the CREATE LOGIN statement on the primary

This script reads the password hash and the SID of the login on the primary replica, then prints the CREATE LOGIN statement to run on each secondary.

DECLARE @user nvarchar(128) = 'app_user';
DECLARE @PasswordHash nvarchar(200);
DECLARE @sid nvarchar(200);
DECLARE @sql nvarchar(max);

SELECT @PasswordHash = master.dbo.fn_varbintohexstr(
           CAST(LOGINPROPERTY(@user, 'PASSWORDHASH') AS varbinary(256)));

SELECT @sid = master.dbo.fn_varbintohexstr(sid)
FROM dbo.syslogins
WHERE name = @user;

SET @sql = 'CREATE LOGIN ' + @user
         + ' WITH PASSWORD=' + @PasswordHash + ' HASHED, SID = ' + @sid;
PRINT @sql;

Copy the printed output and run it on the other replicas.

Create a login with a fixed SID

To find the SID of an existing login:

SELECT sid FROM dbo.syslogins WHERE name = 'app_user';
SELECT sid FROM dbo.sysusers  WHERE name = 'app_user';
SELECT sid FROM sys.database_principals WHERE name = 'app_user';

Create the login with that SID:

CREATE LOGIN app_user WITH PASSWORD = '<PASSWORD>',
    SID = 0x0421442FAD357F4B9D269A52B93B78C8;

Create the user in a database

In a contained database, the user can also be created with a password and a SID:

USE AppDb;
GO
CREATE USER app_user WITH PASSWORD = '<PASSWORD>',
    SID = 0x0421442FAD357F4B9D269A52B93B78C8;

Notes

Mastodon LLIMastodon SocialFosstodon