孤立ユーザーを特定し、ログインに再マッピングする (リストア / 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 とパスワードハッシュを持つログインを再作成するスクリプトを生成する。

使用する場面

別のサーバーでデータベースをリストア / アタッチした後、AG / ログ配布のフェイルオーバー後にアプリケーションが "Login failed for user" を受け取る場合、またはサーバー移行中に。

注意

ALTER USER ... WITH LOGIN はユーザーの SID をログインの SID に変更する。AG ではこの変更がプライマリからセカンダリに反映されるが、セカンダリサーバー上のログインの SID が異なる場合、次のフェイルオーバーで問題が再発する。恒久的な対策は、すべてのレプリカで同じ SID のログインを作成することである。パスワードハッシュは機密情報のため、スクリプトは安全に保管する。包含データベースユーザー (authentication_type = DATABASE) はログインを必要とせず、一覧には表示されない。