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