The server principal '<domain>\User' already exists. But it doesn’t
Interesting little situation occurred today, the person I was working with was trying to add a login to a server and was getting the following error.
“The server principal '<domain>\User' already exists”
They were using a command like this and couldn’t figure out why they were getting the error above because when they looked at the logins setup on the system the login definitely didn’t exist.
CREATE LOGIN [<domain>\<user>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
The key here is that for windows logins the login is not the identifier for the login instead the SID is used, this is a binary value (varbinary(85)).
The nice thing is that the system security functions will work on accounts even if they aren’t logins in the sql server.
The function you need to use in this situation is SUSER_SID, you can pass in a login and get the SID back.
I did a quick query on the system tables for the SID and found the user already existed but with a different login name. The query I used was this.
where SID = SUSER_SID('<domain>\<user>')
How can this happen you ask? Well it is perfectly valid to rename user accounts but the SID stays the same.