SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Todd Engen / Convert binary SID to string / Latest Posts

RE: Convert binary SID to string

LeeFAR — Tue, 13 Sep 2011 15:18:28 GMT

JMartin, did you ever figure out a solution to what you needed to do? I'm in the same boat. I have a SID in binary form and I need to convert it to the human readable version S-0-0-21-0000000000-0000000000-000000000-00000.

RE: Convert binary SID to string

JMartin-392745 — Mon, 01 Aug 2011 05:49:58 GMT

Thanks for this, I was just looking for some way of reversing the varbinary to SID. Trying to see just how much work I need to do as part of an AD migration.Out of curiosity how did you work out the method of reversing the varbianry to get the correct SID?Many thanksJQ

RE: Convert binary SID to string

bogdanblg — Sun, 22 May 2011 12:53:56 GMT

Hello,Thanks for this function. I was in need for the reverse (OS string SID converted to binary sid --> and then user name), but this function was good for explaining how the conversion needs to be done. For those who are looking for the reverse SQL function see below. Reviews/additions are welcome since this is the first release :)[code="sql"]CREATE FUNCTION 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[/code]I use this function for SCOM reports. Somehow the collected user names for security logs are wrong (has to do with different formats of win 2003 and win 2008 event logs), but the SIDs are usable and so I can convert these to user names (without external code).

RE: Convert binary SID to string

Todd Engen — Fri, 17 Oct 2008 07:18:10 GMT

Hi Marcelo,Glad it helped!Just in case you're interested, attached are a CLR based version of SIDToString, as well as a StringToSID.

RE: Convert binary SID to string

mhmbr — Thu, 16 Oct 2008 17:29:47 GMT

EXCELLENT !!I WAS WRINTING AN EXTENDED STORED PROCEDURE WHEN I FOUND THIS ARTICLE.THANKS !!Marcelo Maciel

RE: Convert binary SID to string

corcoranmj — Sun, 25 May 2008 09:44:34 GMT

You wouldn't happen to have (or know of) a function that does the opposite of this, would you? I need a SQL function that will convert from a string SID to binary, and to be honest I'm not good enough with SQL to reverse-engineer yours.

RE: Convert binary SID to string

Mark D Powell — Wed, 02 Apr 2008 10:38:04 GMT

Thanks for the update. -- Mark --

RE: Convert binary SID to string

Todd Engen — Wed, 02 Apr 2008 09:24:12 GMT

Hi Mark,Thanks for the feedback! I should have pointed out this was for Windows authenticated logins, not SQL logins, which follow a different format.I got the idea for this function from a post. Someone was looking to convert and compare to SID's in AD. It could be used for troubleshooting login problems with Windows logins. Here's an example that would show where a domain account might have been deleted and recreated. By comparing the RID's it's easy to see, or rule out, that type of problem.[quote]PSGETSID \\MYDC1 User1 \\MYDC2 User1Returns: S-1-5-21-19403481-1134232155-530107130-3978Then you check sys.server_principals on the SQL Server.SELECT name, dbo.fn_SIDToString(sid) from sys.server_principals.Returns: User1, S-1-5-21-19403481-1134232155-530107130-3953[/quote]Edit: added 'User1' to second part of example.

RE: Convert binary SID to string

Mark D Powell — Wed, 02 Apr 2008 08:17:36 GMT

Being as I am only a part time SQL Server DBA and do not code T-SQL or any other SQL Server applications when would you need to use a function like this.I also think the article should explicitly point out that this is not useful with the SA idsa 0x01 NULLedssa 0x53B7A2241A3E894183F6B64ABA75209E S-83-440305985-1253504643-2652927418-- Mark D Powell --

Convert binary SID to string

Todd Engen — Wed, 20 Feb 2008 20:04:41 GMT

Comments posted to this topic are about the item [B]Convert binary SID to string[/B]