DBAtlas · Database operations atlas

Find orphaned users and remap them to logins (after restore / AG)

SQL ServerSecurity

Command

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

What it does

Finds database users whose SID does not match any server login (orphaned); remaps a user to an existing login and generates a script to recreate logins on another server with the same SID and password hash.

When to use it

After restoring/attaching a database on another server, when the application gets "Login failed for user" after an AG/log shipping failover, or during a server migration.

Caution

ALTER USER ... WITH LOGIN changes the user's SID to the login's; in an AG this change flows from the primary to the secondaries, but if logins on the secondary servers have different SIDs the problem returns on the next failover. The permanent fix is creating logins with the same SID on every replica. The password hash is sensitive; store the script securely. Contained database users (authentication_type = DATABASE) need no login and are not listed.

Open in DBAtlas →

Related