Рано или поздно вам возможно придется столкнуться с запросом к ActiveDirectory.
Собственно сам рецепт запроса прост:
Либо так:
- SELECT primaryGroupID,
- primaryGroupToken,
- employeeid,
- sAMAccountName,
- [name],
- adspath,
- objectGUID,
- info
- FROM OPENQUERY (ADSI, 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''LDAP://DC=MYDOMAIN,DC=RU'' WHERE objectCategory=''Group'' ORDER BY samAccountname');
Либо так:
- SELECT primaryGroupID,
- primaryGroupToken,
- employeeid,
- sAMAccountName,
- [name],
- adspath,
- objectGUID,
- info
- 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');
-
-
Но что делать если у вас в каталоге количество записей много? Хотя бы пара тысяч. Вот тут поможет мой рецептик, до нельзя простой.
-
- declare @samAccountname varchar(64)
- declare @query varchar(4000)
- declare @rc int
-
- if (object_id('tempdb..#usersgrps') is not null) drop table #usersgrps
-
- CREATE TABLE #usersgrps (
- id INT IDENTITY,
- primaryGroupID INT ,
- primaryGroupToken INT ,
- employeeid VARCHAR (15) ,
- sAMAccountName VARCHAR (64) ,
- username VARCHAR (1000),
- adspath VARCHAR (1000),
- isprocessed INT ,
- sid VARBINARY (50),
- info VARCHAR (200) ,
- isgroup BIT
- );
-
- INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
- 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,
- 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''LDAP://DC=MYDOMAIN,DC=RU'' WHERE objectCategory=''User'' ORDER BY samAccountname')
-
- set @rc = @@rowcount
-
- select @samAccountname = max(sAMAccountName)
- from #usersgrps
- where isgroup = 0
-
- 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'
- print @query
- 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 + ''')'
- print @query
-
- while @rc > 0
- begin
- INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
- exec (@query )
- set @rc = @@rowcount
-
- select @samAccountname = max(sAMAccountName)
- from #usersgrps
- where isgroup = 0
-
- 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'
- print @query
- 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 + ''')'
- print @query
- end
-
-
-
- INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
- 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,
- 'SELECT name,adspath,objectGUID,info,primaryGroupID,primaryGroupToken,employeeid, sAMAccountName FROM ''LDAP://DC=MYDOMAIN,DC=RU'' WHERE objectCategory=''Group'' ORDER BY samAccountname')
-
- set @rc = @@rowcount
-
- select @samAccountname = max(sAMAccountName)
- from #usersgrps
- where isgroup = 1
-
- 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'
- print @query
- 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 + ''')'
- print @query
-
- while @rc > 0
- begin
- INSERT #usersgrps (primaryGroupID, primaryGroupToken, employeeid, sAMAccountName, username, adspath, isprocessed, sid, info, isgroup)
- exec (@query )
- set @rc = @@rowcount
-
- select @samAccountname = max(sAMAccountName)
- from #usersgrps
- where isgroup = 1
-
- 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'
- print @query
- 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 + ''')'
- print @query
- end
-
- select * from #usersgrps
