Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert binary SID to string Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 8:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
Comments posted to this topic are about the item Convert binary SID to string
Post #458324
Posted Wednesday, April 02, 2008 8:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 1,343, Visits: 373
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 id

sa 0x01 NULL

edssa 0x53B7A2241A3E894183F6B64ABA75209E S-83-440305985-1253504643-2652927418

-- Mark D Powell --
Post #478542
Posted Wednesday, April 02, 2008 9:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
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.


PSGETSID \\MYDC1 User1 \\MYDC2 User1
Returns: S-1-5-21-19403481-1134232155-530107130-3978

Then 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



Edit: added 'User1' to second part of example.
Post #478619
Posted Wednesday, April 02, 2008 10:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 1,343, Visits: 373
Thanks for the update. -- Mark --
Post #478685
Posted Sunday, May 25, 2008 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 26, 2008 1:58 PM
Points: 1, Visits: 4
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.
Post #506332
Posted Thursday, October 16, 2008 5:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 22, 2008 5:24 PM
Points: 1, Visits: 2
EXCELLENT !!

I WAS WRINTING AN EXTENDED STORED PROCEDURE WHEN I FOUND THIS ARTICLE.

THANKS !!

Marcelo Maciel
Post #587364
Posted Friday, October 17, 2008 7:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
Hi Marcelo,

Glad it helped!

Just in case you're interested, attached are a CLR based version of SIDToString, as well as a StringToSID.





  Post Attachments 
SIDToString_StringToSID_CLR_functions.txt (203 views, 1.07 KB)
Post #587608
Posted Sunday, May 22, 2011 12:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:41 AM
Points: 201, Visits: 420
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 :)

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


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).
Post #1113049
Posted Monday, August 01, 2011 5:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 31, 2014 3:13 PM
Points: 103, Visits: 357
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 thanks

JQ
Post #1151738
Posted Tuesday, September 13, 2011 3:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 5:39 PM
Points: 207, Visits: 197
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.


Post #1174518
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse