How To Find The Missing Logins Between Servers In SQL Server

This happened to discover when I was working on server migration. The logins were all moved from server1 to server2 using the underlying scripts, but we wanted a method to verify them to make sure none of them are missed.

I had come up with a plan to find the missing logins on either of the servers and it worked perfectly. Here is the script that can help you as well to verify the logins where they are missed.

Format:

SELECT p1.name AS [Server1], p2.name AS [Server2]
FROM sys.syslogins p1
full outer join [Your Secondary Server Linked Server].master.sys.syslogins p2 on p1.name = p2.name
where p1.name is null or p2.name is null

Query:

SELECT p1.name AS [Server1], p2.name AS [Server2]
FROM sys.syslogins p1
full outer join [Server2].master.sys.syslogins p2 on p1.name = p2.name
where p1.name is null or p2.name is null

From the result, you may notice that either of the two columns have NULL values. The login corresponding to the NULL values is the missing logins to fix.

For your reference to explore more on an extended stored procedure for logins: xp_logininfo