고아 사용자 찾기 및 로그인에 재매핑 (복원 / AG 후)

명령어

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

하는 일

SID가 어떤 서버 로그인과도 일치하지 않는 데이터베이스 사용자(고아)를 찾는다. 사용자를 기존 로그인에 재매핑하고, 다른 서버에 같은 SID와 비밀번호 해시로 로그인을 다시 만드는 스크립트를 생성한다.

사용 시점

다른 서버에서 데이터베이스를 복원/attach한 후, AG/log shipping 페일오버 후 애플리케이션이 "Login failed for user"를 받을 때, 또는 서버 마이그레이션 중.

주의

ALTER USER ... WITH LOGIN은 사용자의 SID를 로그인의 SID로 바꾼다. AG에서는 이 변경이 primary에서 secondary로 전파되지만, secondary 서버의 로그인 SID가 다르면 다음 페일오버에서 문제가 재발한다. 근본 해결책은 모든 replica에 같은 SID로 로그인을 만드는 것이다. 비밀번호 해시는 민감 정보이므로 스크립트를 안전하게 보관한다. contained database 사용자(authentication_type = DATABASE)는 로그인이 필요 없으며 나열되지 않는다.