Well, this turned out to be an annoying error and one that should have been easier to deal with than I thought. The issue was that we started trying to add a login to an instance as follows;
USE [master]
GO
CREATE LOGIN [Domain\LoginName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
Msg 15025, Level 16, State 2, Line 1
The server principal 'Domain\LoginName' already exists.
So, looking it up on the web yields several results but the key being that this is a duplicate login. Doing a quick search of sys.server_principals yields nothing that matches. Scratch head... Ok, maybe I have a different login name associated with the same SID or the account has a different name.
So, using another instance I run the script to create the account again, grab the SID from there and run the following against the server that I was having issues on;
select * from sys.server_principals where sid = '0x0105000000000005150000008A7A9E01941212592E1E593511110000'
...and nothing. Huh? More confused now. Tried the above using like and nothing. Ok, finally growing a brain (with the help of my manager), executing the following works and reveals the login in question;
select * from sys.server_principals where sid = 0x0105000000000005150000008A7A9E01941212592E1E593511110000
Finally, I get the login that is giving me the problem. Turns out that the account was renamed in AD which allows it to maintain the same SID and so SQL Server continues to recognize that account even though the name is now different.
Sometimes these things are annoying but during the process it reminded me of a few things which is always good.
