When we tried to creating login from SSMS then getting error saying “The server principal already exists”.
I thought that login already exists then verified it from SSMS then I didn’t found that login and i got shocked and assuming might be some bug in SSMS then i tried through T-SQL but i got the same message again.
Actually, I’m not seeing login (server principal), but SQL Server says it’s already ..
Again I checked with sys.server_principals and used sp_helprevlogin but They are not giving me any useful results while using the login name.. After that i start play with SUSER_SID is super useful in this scenario. I queried SUSER_SID passing the login name which I am trying to create, Interestingly it returned a row as you can see below.
I queried sys.server_principals again with SID & Use the SID which came from the above query then i found one existed Login with same SID . Results are shown below
It returned a different login name which already exists on the SQL Server with the same SID of the new login which I’m trying to create. Finally I have to do is drop the old login and Remove DB mapping as needed and create new login, fix DB mapping and permissions.
Hope this helps!!!!