четверг, 29 сентября 2011 г.

MSSQL : LDAP запрос.

Всем добрый день.
Рано или поздно вам возможно придется столкнуться с запросом к ActiveDirectory.
Собственно сам рецепт запроса прост:

Либо так:
  1. SELECT primaryGroupID,
  2.        primaryGroupToken,
  3.        employeeid,
  4.        sAMAccountName,
  5.        [name],
  6.        adspath,
  7.        objectGUID,
  8.        info
  9. FROM OPENQUERY (ADSI, 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''LDAP://DC=MYDOMAIN,DC=RU'' WHERE objectCategory=''Group'' ORDER BY samAccountname');


Либо так:
  1. SELECT primaryGroupID,
  2.        primaryGroupToken,
  3.        employeeid,
  4.        sAMAccountName,
  5.        [name],
  6.        adspath,
  7.        objectGUID,
  8.        info
  9. FROM OPENQUERY (ADSI, N'<LDAP://DC=MORTON,DC=RU>;(&(objectClass=User)(objectCategory=Person)(|(sAMAccountName=a*)(sAMAccountName=b*)(sAMAccountName=c*)));primaryGroupID, primaryGroupToken,employeeid, sAMAccountName, name, adspath, objectGUID, info');
  10.  
  11.  


Но что делать если у вас в каталоге количество записей много? Хотя бы пара тысяч. Вот тут поможет мой рецептик, до нельзя простой.

  1.  
  2. declare @samAccountname varchar(64)
  3. declare @query varchar(4000)
  4. declare @rc int
  5.  
  6. if (object_id('tempdb..#usersgrps') is not null) drop table #usersgrps
  7.  
  8.         CREATE TABLE #usersgrps (
  9.             id INT IDENTITY,
  10.             primaryGroupID INT ,
  11.             primaryGroupToken INT ,
  12.             employeeid VARCHAR (15) ,
  13.             sAMAccountName VARCHAR (64) ,
  14.             username VARCHAR (1000),
  15.             adspath VARCHAR (1000),
  16.             isprocessed INT ,
  17.             sid VARBINARY (50),
  18.             info VARCHAR (200) ,
  19.             isgroup BIT
  20.         );
  21.  
  22. INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
  23. SELECT TOP 900 primaryGroupID,
  24.                primaryGroupToken,
  25.                employeeid,
  26.                sAMAccountName,
  27.                [dbo].[f_string_ad_clear]([name]),
  28.                [dbo].[f_string_ad_clear](adspath),
  29.                0,
  30.                objectGUID,
  31.                [dbo].[f_string_ad_clear](info),
  32.                0
  33. FROM OPENQUERY(ADSI,
  34.         'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''LDAP://DC=MYDOMAIN,DC=RU'' WHERE objectCategory=''User'' ORDER BY samAccountname')
  35.  
  36. set @rc = @@rowcount
  37.  
  38. select @samAccountname = max(sAMAccountName)
  39. from #usersgrps
  40. where isgroup = 0
  41.  
  42. select @query = 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''''LDAP://DC=MYDOMAIN,DC=RU'''' WHERE objectCategory=''''User'''' and samAccountname > '''''+@samAccountname+''''' ORDER BY samAccountname'
  43. print @query
  44. select @query = 'SELECT TOP 900 primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, [dbo].[f_string_ad_clear]([name]), [dbo].[f_string_ad_clear](adspath), 0, objectGUID, [dbo].[f_string_ad_clear](info), 0 FROM OPENQUERY(ADSI, ''' + @query + ''')'
  45. print @query
  46.  
  47. while @rc > 0
  48.     begin
  49.         INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
  50.         exec (@query )
  51.         set @rc = @@rowcount
  52.  
  53.         select @samAccountname = max(sAMAccountName)
  54.         from #usersgrps
  55.         where isgroup = 0
  56.  
  57.         select @query = 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''''LDAP://DC=MYDOMAIN,DC=RU'''' WHERE objectCategory=''''User'''' and samAccountname > '''''+@samAccountname+''''' ORDER BY samAccountname'
  58.         print @query
  59.         select @query = 'SELECT TOP 900 primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, [dbo].[f_string_ad_clear]([name]), [dbo].[f_string_ad_clear](adspath), 0, objectGUID, [dbo].[f_string_ad_clear](info), 0 FROM OPENQUERY(ADSI, ''' + @query + ''')'
  60.         print @query
  61.     end
  62.  
  63.  
  64.  
  65. INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
  66. SELECT TOP 900 primaryGroupID,
  67.                primaryGroupToken,
  68.                employeeid,
  69.                sAMAccountName,
  70.                [dbo].[f_string_ad_clear]([name]),
  71.                [dbo].[f_string_ad_clear](adspath),
  72.                0,
  73.                objectGUID,
  74.                [dbo].[f_string_ad_clear](info),
  75.                1
  76. FROM OPENQUERY(ADSI,
  77.         'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''LDAP://DC=MYDOMAIN,DC=RU'' WHERE objectCategory=''Group'' ORDER BY samAccountname')
  78.  
  79. set @rc = @@rowcount
  80.  
  81. select @samAccountname = max(sAMAccountName)
  82. from #usersgrps
  83. where isgroup = 1
  84.  
  85. select @query = 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''''LDAP://DC=MYDOMAIN,DC=RU'''' WHERE objectCategory=''''Group'''' and samAccountname > '''''+@samAccountname+''''' ORDER BY samAccountname'
  86. print @query
  87. select @query = 'SELECT TOP 900 primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, [dbo].[f_string_ad_clear]([name]), [dbo].[f_string_ad_clear](adspath), 0, objectGUID, [dbo].[f_string_ad_clear](info), 1 FROM OPENQUERY(ADSI, ''' + @query + ''')'
  88. print @query
  89.  
  90. while @rc > 0
  91.     begin
  92.         INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
  93.         exec (@query )
  94.         set @rc = @@rowcount
  95.  
  96.         select @samAccountname = max(sAMAccountName)
  97.         from #usersgrps
  98.         where isgroup = 1
  99.  
  100.         select @query = 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''''LDAP://DC=MYDOMAIN,DC=RU'''' WHERE objectCategory=''''Group'''' and samAccountname > '''''+@samAccountname+''''' ORDER BY samAccountname'
  101.         print @query
  102.         select @query = 'SELECT TOP 900 primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, [dbo].[f_string_ad_clear]([name]), [dbo].[f_string_ad_clear](adspath), 0, objectGUID, [dbo].[f_string_ad_clear](info), 1 FROM OPENQUERY(ADSI, ''' + @query + ''')'
  103.         print @query
  104.     end
  105.  
  106. select * from #usersgrps