SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert binary SID to string


Convert binary SID to string

Author
Message
Todd Engen
Todd Engen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1779 Visits: 6336
Comments posted to this topic are about the item Convert binary SID to string
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 466
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 --
Todd Engen
Todd Engen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1779 Visits: 6336
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.
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 466
Thanks for the update. -- Mark --
corcoranmj
corcoranmj
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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.
mhmbr
mhmbr
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 2
EXCELLENT !!

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

THANKS !!

Marcelo Maciel
Todd Engen
Todd Engen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1779 Visits: 6336
Hi Marcelo,

Glad it helped!

Just in case you're interested, attached are a CLR based version of SIDToString, as well as a StringToSID.
Attachments
bogdanblg
bogdanblg
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 459
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 Smile


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).
JMartin-392745
JMartin-392745
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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
LeeFAR
LeeFAR
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 Visits: 326
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search