|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, February 17, 2012 8:16 AM
Points: 1,290,
Visits: 1,916
|
|
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?
Jason Shadonix
MCTS, SQL 2005
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Monday, February 20, 2012 1:29 AM
Points: 2,177,
Visits: 3,197
|
|
I am not sure, but I assume you are looking for SELECT SUSER_SID()
Best Regards,
Chris Büttner
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, February 17, 2012 8:16 AM
Points: 1,290,
Visits: 1,916
|
|
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.
Jason Shadonix
MCTS, SQL 2005
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Sunday, January 29, 2012 2:12 PM
Points: 1,073,
Visits: 6,296
|
|
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.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, May 17, 2010 7:14 AM
Points: 19,
Visits: 1,196
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Sunday, January 29, 2012 2:12 PM
Points: 1,073,
Visits: 6,296
|
|
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 = 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/
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, May 17, 2010 7:14 AM
Points: 19,
Visits: 1,196
|
|
Cool! This works like a charm
Thank you very much for this!
Cheers,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
|
|
|
|
