When working with multiple SQL Server instances—such as during database migrations, DR setup, or server refresh activities—DBAs often encounter issues related to login and user mappings.
In SQL Server, logins are associated with databases through a Security Identifier (SID). When a database is restored or moved to another server, the database users retain their original SID. If the login on the new server has a different SID, the mapping breaks, resulting in orphaned users and login failures.
To avoid this, it is important to compare login SIDs between servers and ensure they match.
Script to Compare Login SID Between Two SQL Servers
The following script compares SQL login SIDs between the local SQL Server instance and a remote SQL Server instance using a linked server.
SELECT ISNULL(L.name, R.name) AS LoginName, L.sid AS Local_SID, R.sid AS Remote_SID,
CASE
WHEN L.sid = R.sid THEN 'MATCH'
WHEN L.sid IS NULL THEN 'Missing on Local'
WHEN R.sid IS NULL THEN 'Missing on Remote'
ELSE 'SID MISMATCH'
END AS SID_Status
FROM sys.sql_logins L
FULL OUTER JOIN
OPENQUERY([FERACK_PR],
'
SELECT name, sid FROM sys.sql_logins
') R
ON L.name = R.name
ORDER BY SID_Status;
Example Output
| LoginName | Local_SID | Remote_SID | SID_Status |
|---|---|---|---|
| AppUser | 0xABC123 | 0xABC123 | MATCH |
| ReportUser | 0xA11F23 | 0xBB8F21 | SID MISMATCH |
| DevUser | NULL | 0xC12F99 | Missing on Local |
| TestUser | 0xF11239 | NULL | Missing on Remote |
No comments:
Post a Comment