July 12, 2006 at 6:28 am
I am using
select SUSER_SID('xxx\fredbloggs') as "numbersid"
to get out a user's NT Sid in the format
0x010200000000000513000000776EB56041091A2A564DD600F5010000
but I need to convert this into the format
S-1-5-21-1622502031-706434993-14174502-4769
either via SQL or an ActiveX in a DTS package.
Does anyone know how this can be achieved? I've been trying to get the ConvertSidtoStringSid (and the reverse) function to work without success.
TIA
July 13, 2006 at 10:57 am
July 14, 2006 at 2:59 am
Thanks for the link but I'm having huge trouble getting that to work in a DTS package so I was hoping there was some nice way of doing it via SQL.
July 14, 2006 at 11:26 am
Here is a conversion in T-SQL you could turn into a function. I'm not sure how you would do it in DTS with ActiveX without the bigint and varbinary data types.
DECLARE
@sid varbinary(85)
SET @sid = 0x0106000000000009010000003F60DEE7193458D3D190E51C09BEDE59E274DDF2
DECLARE @s-2 varchar(255)
DECLARE @r smallint, @m smallint, @i bigint
SET @r = substring(@sid,1,1) -- revision
SET @s-2 = 'S-' + cast(@r AS varchar)
-- The dbo SID is just 0x01, check before going further
IF len(@sid) > 1 begin
SET @i = substring(@sid,3,6) -- authority
SET @s-2 = @s-2 + '-' + cast(@i AS varchar)
SET @m = 9 -- Add subauthorities until reaching the end of the SID
-- Each subauthority DWORD value is four bytes in little-endian order (least-significant byte first)
-- @i was declared bigint (and the high-order byte handled separately) to avoid overflow errors
SET @i = substring(@sid, @m + 3, 1)
SET @i = substring(@sid, @m, 1) + 256 * (substring(@sid, @m + 1, 1) + 256 * (substring(@sid, @m + 2, 1) + 256 * @i))
SET @s-2 = @s-2 + '-' + cast(@i AS varchar)
END
END
PRINT @sid
PRINT @s-2
0x0106000000000009010000003F60DEE7193458D3D190E51C09BEDE59E274DDF2
S-1-9-1-3890110527-3545773081-484806865-1507769865-4074599650
I verified this code in .NET with the Security.Principle class. Both produced the same string SID.
Imports
System.Security.Principal
Public Class Form2
Private Sub Form2_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
' SID = 0x0106000000000009010000003F60DEE7193458D3D190E51C09BEDE59E274DDF2
Dim bSID As Byte() = {&H1, &H6, &H0, &H0, &H0, &H0, &H0, &H9, &H1, &H0, &H0, &H0, &H3F, &H60, &HDE, &HE7, &H19, &H34, &H58, &HD3, &HD1, &H90, &HE5, &H1C, &H9, &HBE, &HDE, &H59, &HE2, &H74, &HDD, &HF2}
Dim oSID As New SecurityIdentifier(bSID, 0)
txtStringSID.Text = oSID.ToString
End Sub
End Class
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply