SID HISTORY: Fixing SQL

SQL server logins may have old SIDs. This doesn't fix them (because we didn't have this particular problem at work) but shows if there's any that need to be fixed.

-- WE NEED NEW FUNCTION TO CONVERT SQL BINARY SID TO STANDARD SDDL SID
CREATE FUNCTION fn_SIDToString_foo
(
  @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
GO

-- QUERY LIST OF ALL USERS AND CONVERT SID
USE master
select loginname,name,dbname,dbo.fn_SIDToString_foo(sid),createdate,updatedate from dbo.syslogins ORDER BY sid
GO

-- CLEANUP
DROP FUNCTION fn_SIDToString_foo
GO