DBAtlas · Veritabanı operasyon atlası

Orphaned user'ları bul ve login'e bağla (restore / AG sonrası)

SQL ServerGüvenlik

Komut

USE [Musteri];
-- Login'i olmayan veritabani kullanicilari:
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;

-- Ayni isimli login varsa SID'i eslestir:
ALTER USER [app_user] WITH LOGIN = [app_user];

-- Kalici cozum: kaynak sunucuda SID + parola hash ile CREATE LOGIN uret, hedefte calistir:
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';

Ne yapar?

Veritabanındaki kullanıcının SID'i sunucudaki hiçbir login'le eşleşmediği (orphaned) durumları bulur; kullanıcıyı mevcut login'e yeniden bağlar ve login'leri diğer sunucuda aynı SID ve parola hash'iyle yeniden oluşturacak script üretir.

Ne zaman kullanılır?

Veritabanını başka sunucuya restore/attach ettikten sonra, AG/log shipping failover'ında uygulama "Login failed for user" alırken, sunucu migration'ında.

Dikkat

ALTER USER ... WITH LOGIN kullanıcının SID'ini login'inkine çevirir; AG'de bu değişiklik primary'den secondary'lere taşınır, ama secondary sunuculardaki login SID'leri farklıysa sonraki failover'da sorun geri gelir. Kalıcı çözüm login'leri tüm replikalarda aynı SID ile oluşturmaktır. Parola hash'i hassas veridir; scripti güvenli sakla. Contained database user'ları (authentication_type = DATABASE) login gerektirmez ve listede yer almaz.

DBAtlas'ta aç →

İlgili