Convert binary SID to string

• reza.azimi

  SSC Rookie

  Points: 49

  More actions

  July 18, 2018 at 12:04 pm

  #1998075

  reza.azimi - Wednesday, July 18, 2018 12:03 PM

  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?

  or what I actually want is of course to return the SID for those users I have in focus from ADSI.

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  July 18, 2018 at 12:10 pm

  #1998078

  reza.azimi - Wednesday, July 18, 2018 12:04 PM

  reza.azimi - Wednesday, July 18, 2018 12:03 PM

  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?

  or what I actually want is of course to return the SID for those users I have in focus from ADSI.

  I assume you would pass in "tblADuser.objectsid" to that function....

  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