Convert binary SID to string

Question

Convert binary SID to string

Todd Engen

SSCrazy Eights

Points: 9869
More actions
February 20, 2008 at 8:04 pm

#203570

Comments posted to this topic are about the item Convert binary SID to string

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 1

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

sa0x01NULL

edssa0x53B7A2241A3E894183F6B64ABA75209ES-83-440305985-1253504643-2652927418

-- Mark D Powell --

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 2

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 SSCarpal Tunnel Points: 4775 More actions · Answer 3

Mark D Powell

SSCarpal Tunnel

Points: 4775

April 2, 2008 at 10:38 am

#797386

Thanks for the update. -- Mark --

corcoranmj Valued Member Points: 59 More actions · Answer 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 SSC Journeyman Points: 97 More actions · Answer 5

EXCELLENT !!

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

THANKS !!

Marcelo Maciel

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 6

Hi Marcelo,

Glad it helped!

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

bogdanblg SSC Veteran Points: 296 More actions · Answer 7

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).

JMartin-392745 Ten Centuries Points: 1279 More actions · Answer 8

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 SSCarpal Tunnel Points: 4644 More actions · Answer 9

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 1:53 pm

#1877964

Thanks for the script.

boevik SSC Rookie Points: 29 More actions · Answer 11

The script have a small bug.
Instead of use LEN must use DATALENGTH.
Because if the binary data ended by 32 (it's space in ASCII), function LEN returns length minus 1.

reza.azimi SSC Rookie Points: 49 More actions · Answer 12

I have tried to use your function - BintoSIDstring but I wonder why do I see the same SID value all the time?

this is my query:

SELECT top(900)

SUSER_SID(), mds.dbo.fn_SIDToString( SUSER_SID()) as Login_SID,

* FROM OpenQuery (
ADSI,
'SELECT title, objectsid, sAMAccountName,
givenname
FROM ''LDAP://dansk-retursystem.dk''
WHERE objectClass = ''User''
') as tblADuser

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 13

reza.azimi - Wednesday, July 18, 2018 11:55 AM
I have tried to use your function - BintoSIDstring but I wonder why do I see the same SID value all the time?
this is my query:
SELECT top(900) SUSER_SID(), mds.dbo.fn_SIDToString( SUSER_SID()) as Login_SID, *
FROM OpenQuery (ADSI,
'SELECT title, objectsid, sAMAccountName, givenname
FROM ''LDAP://dansk-retursystem.dk''
WHERE objectClass = ''User''
') as tblADuser

You are passing in "SUSER_SID()", which is the SID of the current user who is executing that statement. That value is not going to change per row.

Take care,
Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

reza.azimi SSC Rookie Points: 49 More actions · Answer 14

Solomon Rutzky - Wednesday, July 18, 2018 12:02 PM
reza.azimi - Wednesday, July 18, 2018 11:55 AM
I have tried to use your function - BintoSIDstring but I wonder why do I see the same SID value all the time?
this is my query:
SELECT top(900) SUSER_SID(), mds.dbo.fn_SIDToString( SUSER_SID()) as Login_SID, *
FROM OpenQuery (ADSI,
'SELECT title, objectsid, sAMAccountName, givenname
FROM ''LDAP://dansk-retursystem.dk''
WHERE objectClass = ''User''
') as tblADuser
You are passing in "SUSER_SID()", which is the SID of the current user who is executing that statement. That value is not going to change per row.
Take care,
Solomon..

okay so how do I tell it to take the suser_sid for each row?