February 11, 2008 at 11:48 am
We need to be able to get the active directory SID for a user logged into SQL using Windows Authentication (Not the SQL SID, the AD SID).
Does anyone know if there is a built-in SQL function to do that?
The Redneck DBA
February 11, 2008 at 3:53 pm
I am not sure, but I assume you are looking for SELECT SUSER_SID()
Best Regards,
Chris Büttner
February 11, 2008 at 5:16 pm
I found that. But it returns something different than the SID in active directory.
It looks like SQL keeps it's own internal SIDs seperate from the active directory SID.
The Redneck DBA
February 11, 2008 at 7:54 pm
That should be the same SID, different format. To convert that to a string like 'S-1-5-' here are a couple ways to do it.
Write a function to convert it that looks something like this:
Starting at the 8th byte take four bytes at a time from left to right. Then take each of those sequences and read from right left, then convert to decimal.
For example, if the last four bytes returned by SUSER_SID were B9 0D 00 00 that would be read as 00 00 0D B9 and convert to 3513 decimal.
Another way would be to create a CLR function that uses the System.Security.Principal class to handle the conversion.
May 11, 2010 at 12:21 am
Hi there,
well, I'm facing the same challenge: I need to convert a SID like "S-1-5-21-3188961675-671516999-2543839186-1000" into this "0x0105000000000005150000008BB113BE47890628D2E79F97E8030000" (or vice-versa).
Todd actually described a solution, but I'm afraid this exceeds my TSQL programming skills ... Maybe you could point me to some example/link etc.? That would be great!
Jörg A. Stryk
MVP - MS Dynamics NAV
May 17, 2010 at 6:17 am
Hi Jörg,
Here's one way to get the binary value from the string as long as it looks like S-1-5-21-aaaaa-bbbbb-ccccc-ddddd.
/*
S = security identifier
1 = revision 1 of the NT SID format
5 = there are five 32 bit words following
21 = this SID has the standard NT identifier authority format
S-1-5-21-aaaaa-bbbbb-ccccc-ddddd
*/
DECLARE @MYSID AS VARCHAR(255)
SET @MYSID = 'S-1-5-21-3188961675-671516999-2543839186-1000'
DECLARE @a AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT
SET @MYSID = REVERSE(@MYSID)
SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @b-2 = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @a = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
-- Prefix the binary value with S-1-5-21 and reverse the byte order for each group.
PRINT 0x010500000000000515000000
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))
And this script will convert the other direction: http://www.sqlservercentral.com/scripts/SID/62274/[/url]
May 17, 2010 at 7:16 am
Cool! This works like a charm:-)
Thank you very much for this!
Cheers,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
January 8, 2014 at 1:25 am
Hi,
can anybody show me the reverse way?
(SID as 0x.. into S-34-... string).
thanx a lot
January 8, 2014 at 7:48 am
khenlevy (1/8/2014)
can anybody show me the reverse way?
DECLARE @sid varbinary(39)
SET @sid = 0x0105000000000005150000008BB113BE47890628D2E79F97E8030000
SELECT 'S-1-5-21-'
+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,13,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,17,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,21,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,25,4),1)) as varbinary(4)) as bigint) as varchar(10))
Far away is close at hand in the images of elsewhere.
Anon.
January 8, 2014 at 8:42 am
Thank you very much!
Working perfectly!
April 6, 2014 at 1:14 am
/*
This script was very useful for one of my project, thanks for sharing it, I have pasted complete script if with creating function & uses with one of example:-
*/
Use MyDB
Go
If OBJECT_ID (N'dbo.sidconvert', N'FN') IS NOT NULL
DROP FUNCTION sidconvert;
GO
CREATE FUNCTION dbo.sidconvert (@MYSID AS VARCHAR(max))
Returns varbinary(256)
As
Begin
DECLARE @a AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT
SET @MYSID = REVERSE(@MYSID)
SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @b-2 = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @a = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
return 0x010500000000000515000000
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))
End
Go
Select t1.Resource, t1.SID, SUSER_Sname(dbo.sidconvert(t1.SID)) 'AD_Object'
From Mydb.dbo.table1 t1
Order by t1.Resource
Go
DROP FUNCTION sidconvert;
/*
Output
Resource SIDAd-Object
Conf-room1S-1-5-21-2620262765-442730090-2808145922-1207domain\user1
Conf-room2S-1-5-21-2620262765-442730090-2808145922-1151domain\user2
Regards
Pradeep Papnai
*/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply