Find SQL User name with "Windows Login" SID

DenSter

Alright so I spent hours today trying to figure this out, and since I could not find any one place that has all the information in one place, I figured I'd share it here. This is to find the user's name in SQL Server, based on the Windows Login for the NAV database.

The "Windows Login" table in the NAV database stores the user's SID in text format. What is missing there is the user name. SQL Server has a couple of places that stores the user name and SID (sysusers for instance, or sys.syslogins), so you'd think that it would be easy to get the user name from SQL Server, all you'd have to do is query where those SID values are equal. This is where it gets complicated because even though they represent the same thing, they are formatted differently.

In the "Windows Login" table it is formatted something like 's-x-x-xx-xxxxx-xxxxx-xxxxx-xxxxx', and in SQL Server it's a hexadecimal value, so it looks something like '0x01050000000000............'. To link the two together, you first have to create two user-defined functions. The first translates the NAV-SID to SQL-SID, and the other goes the other way around. Then, you can use those functions to link the NAV tables to the SQL tables.

Here are the functions (fn_SIDToString from SQL Server Central, you need to be logged in to see the script. Google 'fn_StringToSID' to find the other script, it's in the comment section of another thread):

-- to translate the SID in AD format (e.g. from the NAV table "Windows Login") to binary format
CREATE FUNCTION [dbo].[fn_StringToSID]
(
        @xStrSid VARCHAR(100)
)
RETURNS VARBINARY(100)
AS 
BEGIN                    
	DECLARE @xBinSid VARBINARY(100)         
	SET @xBinSid = CAST(CAST(SUBSTRING(@xStrSid , 3,1) AS TINYINT) AS VARBINARY)        
	SET @xBinSid = @xBinSid + 0x05        
	SET @xBinSid = @xBinSid + CAST(CAST(SUBSTRING(@xStrSid , 5,1) AS TINYINT) AS BINARY(6))        
	SET @xStrSid = SUBSTRING(@xStrSid,7,LEN(@xStrSid)-6)        
	
	DECLARE @oneInt BIGINT        
	
	WHILE CHARINDEX('-',@xStrSid) > 0        
	BEGIN                
		SET @oneInt = CAST(SUBSTRING(@xStrSid,1,CHARINDEX('-',@xStrSid)-1) AS BIGINT)                
		SET @xBinSid = @xBinSid + CAST(REVERSE(CAST(@oneInt AS VARBINARY)) AS VARBINARY(4))                
		SET @xStrSid = SUBSTRING(@xStrSid,CHARINDEX('-',@xStrSid)+1,LEN(@xStrSid))        
	END        
	
	SET @oneInt = CAST(@xStrSid AS BIGINT)        
	SET @xBinSid = @xBinSid + CAST(REVERSE(CAST(@oneInt AS VARBINARY)) AS VARBINARY(4))
	-- select @xBinSid , suser_sname(@xBinSid)        
	
	RETURN ( @xBinSid ) 
END

-- to translate SID in binary format to AD format
CREATE FUNCTION [dbo].[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

You run those scripts in the Master database, so it's easily accessible from any query.

Here's a sample query to show the content from both "Windows Login" and sys.syslogins using the functions:

-- sample script to show the link between SID in "Windows Login" and sys.syslogins
SELECT SID as NAVSID,[dbo].[fn_StringToSID](SID) as SQLSID
  FROM [Demo Database NAV (6-0)].[dbo].[Windows Login]

  
SELECT SL.sid as SQLSID, SL.name as SQLNAME, WL.SID as NAVSID 
FROM sys.syslogins as SL JOIN [Windows Login] as WL
ON SL.sid = [dbo].[fn_StringToSID](WL.SID)

SELECT * From [Windows Login] WHERE [Windows Login].SID = dbo.fn_SIDToString(SUSER_SID())

Credit and special thanks should go to Owen and Michael and everyone else that pitched in =D> Thanks guys

DenSter

An alternative way:

/* Created to print user name based on SID value in NAV */
/* Execute script against the NAV database */
set nocount on

declare
      @SID varchar(255),
      @OrigSID varchar(255),
      @A bigint,
      @B bigint,
      @C bigint,
      @D bigint,
      @Value varbinary(255)

declare PrintUser INSENSITIVE CURSOR FOR
select SID from [Windows Login]
open PrintUser

fetch next from PrintUser into @SID

while (@@fetch_status <> -1)
begin
      select @OrigSID = @SID
      SET @SID = REVERSE(@SID)
      SET @D = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
      SET @C = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
      SET @B = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
      SET @A = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))

      -- Prefix the binary value with S-1-5-21 and reverse the byte order for each group.
      select @Value = 
            0x010500000000000515000000
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A))) 
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B))) 
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))

      select name, sid as 'SQL SID', @OrigSID as 'NAV SID' from master..syslogins where SID = @Value

      fetch next from PrintUser into @SID
end
deallocate PrintUser

Special thanks to Nick

acyvas

Hi here is less readable version of DenSter code but useful in situations where you are not able to create functions. like creating a cube.
Just replace YOUR_NAV_DATABASE and you are good to go

Select  NAV_DB.SID as NAVSID, mdb.name, mdb.sid as mdbsid from
(SELECT     SID, 
 cast(
            0x010500000000000515000000
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),cast (REVERSE(SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))+1,255),CHARINDEX('-',SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))+1,255))+1,255),1,CHARINDEX('-',SUBSTRING(SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))+1,255),CHARINDEX('-',SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))+1,255))+1,255))-1))  as bigint)))) 
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),cast (REVERSE(SUBSTRING(SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))+1,255),1,CHARINDEX('-',SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))+1,255))-1))as bigint))))
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),cast (REVERSE(SUBSTRING(SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255),1,CHARINDEX('-',SUBSTRING(REVERSE(SID),CHARINDEX('-',REVERSE(SID))+1,255))-1))as bigint))))
            + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),cast (REVERSE(SUBSTRING(REVERSE(SID),1,CHARINDEX('-',REVERSE(SID))-1))as bigint))))
as varbinary(255) ) as SQLSID 
FROM         YOUR_NAV_DATABASE.dbo.[Windows Login]) as NAV_DB inner join  master..sysusers  as mdb on (NAV_DB.SQLSID = mdb.SID)

lukewaw

Hi. I know it's quite old, but there is a simpler way to get the user name by AD SID stored in Windows Login NAV table. Function used are SID_BINARY() to changed text version of SID to binary, then SUSER_SNAME() to retrieve the user name, so final function is like: SUSER_SNAME(SID_BINARY([SID])).