Encontrar usuarios huérfanos y volver a asociarlos a logins (tras restore / AG)

Comando

USE [Musteri];
-- Database users without a matching login:
SELECT dp.name AS user_name, dp.type_desc, dp.authentication_type_desc, dp.sid, dp.create_date
FROM   sys.database_principals dp
  LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE  sp.sid IS NULL
  AND  dp.type IN ('S', 'U', 'G')
  AND  dp.authentication_type_desc IN (N'INSTANCE', N'WINDOWS')
  AND  dp.principal_id > 4;

-- If a login with the same name exists, remap the SID:
ALTER USER [app_user] WITH LOGIN = [app_user];

-- Permanent fix: generate CREATE LOGIN with SID + password hash on the source, run it on the target:
SELECT N'CREATE LOGIN ' + QUOTENAME(sp.name)
     + N' WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX), LOGINPROPERTY(sp.name, 'PasswordHash'), 1)
     + N' HASHED, SID = ' + CONVERT(NVARCHAR(MAX), sp.sid, 1)
     + N', CHECK_POLICY = OFF;' AS create_login_stmt
FROM   sys.server_principals sp
WHERE  sp.type = 'S' AND sp.name = N'app_user';

Qué hace

Encuentra usuarios de base de datos cuyo SID no coincide con ningún login del servidor (huérfanos); vuelve a asociar un usuario a un login existente y genera un script para recrear los logins en otro servidor con el mismo SID y hash de contraseña.

Cuándo usarlo

Tras restaurar/adjuntar una base de datos en otro servidor, cuando la aplicación recibe "Login failed for user" tras un failover de AG/log shipping o durante una migración de servidor.

Precaución

ALTER USER ... WITH LOGIN cambia el SID del usuario al del login; en un AG este cambio fluye del primario a los secundarios, pero si los logins de los servidores secundarios tienen SID distintos el problema vuelve en el siguiente failover. La solución permanente es crear los logins con el mismo SID en todas las réplicas. El hash de contraseña es sensible; guarde el script de forma segura. Los usuarios de bases de datos contenidas (authentication_type = DATABASE) no necesitan login y no se listan.