Sometimes login mapping issues exist where you least expect them

This is not the traditional SQL login SID mismap issue that is frequently encountered and discussed here. This mismap was a new one on me so I thought id document it.

When trying to add a new Windows login for SQL Server 2005 or 2008 (probably earlier versions but not tested) you may wind up with this error message

Msg 15025, Level 16, State 2, Line 1

The server principal 'DXXXX\UXXX' already exists.

Contrary to the error message, If we need to proove the account doesnt exist, the following code should suffice

select name from sys.server_principals where name = ‘DXXXX\UXXX’

SQL wont let you create an account because it thinks it already exists, but clearly the account doesnt exist.

How did this happen?

If a login was created previously for a windows account that has since been renamed in the active directory you cannot grant the new userid access to the SQL server because the SID already exists in SQL and you cannot duplicate it.  This occurs when renaming an account in Active directory, because the SID is reused instead of recreated.

Find the pre-existing SID

The SID from Active directory can be obtained many ways, the one I use is PsGetsid which can be obtained here the syntax you’d use for this is

PsGetSid DOMAIN\USER (or DXXXX\UXXX from earlier)

This will return the SID in the SDDL Format of S-x-x-x-x

Unfortunately SQL Stores the binary representation of the SID not the SDDL format.  You have several choices to do the mapping, you can hand figure out the SDDL mappings from sys.server_principals using this page  Or the better way -> Todd Engen had found some need to convert this before and designed a great function to do this conversion from binary to SDDL , im only going to list it here for completeness, the original is found here
CREATE FUNCTION fn_SIDToString
(
@BinSID AS VARBINARY(100)
)

RETURNS VARCHAR(100)
AS BEGIN
 
  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
 
  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT
 
  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
 
  SET @j = 9
  SET @i = LEN(@BinSID)
 
  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END

 

After creating this function use it like so

select name from sys.server_principals where dbo.fn_SIDToString(sid)=‘S-X-X-X-X’ where the ‘S-X-X-X-X’ is the SID obtained earlier from PsGetSid

This should return the name of the user that has the offending “duplicate” SID

Once you know the logon ID that has been renamed you can script out the permissions of that ID (hopefully it doesn’t own any objects), drop it, and recreate it with the appropriate name and grant any additional permissions