Trouver les utilisateurs orphelins et les rattacher à des logins (après restauration / AG)

Commande

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';

Ce que fait la commande

Trouve les utilisateurs de base dont le SID ne correspond à aucun login serveur (orphelins) ; rattache un utilisateur à un login existant et génère un script pour recréer les logins sur un autre serveur avec le même SID et le même hash de mot de passe.

Quand l'utiliser

Après la restauration/l'attachement d'une base sur un autre serveur, quand l'application reçoit "Login failed for user" après un basculement AG/log shipping, ou lors d'une migration de serveur.

Attention

ALTER USER ... WITH LOGIN remplace le SID de l'utilisateur par celui du login ; dans un AG, cette modification se propage du primaire vers les secondaires, mais si les logins des serveurs secondaires ont des SID différents, le problème revient au basculement suivant. La correction durable consiste à créer les logins avec le même SID sur chaque réplica. Le hash du mot de passe est sensible ; stockez le script de façon sécurisée. Les utilisateurs de base contenue (authentication_type = DATABASE) n'ont pas besoin de login et ne sont pas listés.