SQL Server中从Active Directory获取超901条记录的解决方法求助

你的问题是AD默认限制了单次查询的返回条数（通常为1000条以内），以下是几种可行的解决方法：

方法1：使用LDAP分页+循环分批拉取（推荐） #

通过按唯一标识排序，结合LDAP分页参数，循环拉取所有符合条件的记录，直到取完为止：

DECLARE @PageSize INT = 900;
CREATE TABLE #ADUsers (
    EmpCode VARCHAR(100),
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(200),
    Telephone VARCHAR(50),
    DisplayName VARCHAR(200),
    Mobile VARCHAR(50),
    Designation VARCHAR(200),
    [Status] VARCHAR(50),
    UserAccountControl INT,
    Office VARCHAR(100)
);

WHILE 1 = 1
BEGIN
    INSERT INTO #ADUsers
    SELECT 
        UPPER(sAMAccountName)[EmpCode], 
        givenName [FirstName],
        ISNULL(sn, '') [LastName],
        ISNULL(mail, 'NA') [Email],
        telephoneNumber [Telephone], 
        cn [DisplayName],
        Mobile, 
        title [Designation],
        CASE 
            WHEN UserAccountControl = 512 THEN 'Active' 
            WHEN UserAccountControl = 514 THEN 'In-Active' 
            WHEN UserAccountControl = 66048 THEN 'Active, password never expires' 
            WHEN UserAccountControl = 66050 THEN 'In-Active, password never expires'  
            ELSE 'NA' 
        END [Status],
        UserAccountControl,
        UPPER(ISNULL(physicalDeliveryOfficeName, 'NA')) [Office]
    FROM OPENQUERY(ADSI, 
        'SELECT sAMAccountName, givenName, sn, mail, telephoneNumber, cn, Mobile, title, UserAccountControl, physicalDeliveryOfficeName
         FROM ''LDAP://domain.local/OU=Company,DC=domain,DC=local'' 
         WHERE objectClass = ''User'' 
         AND sAMAccountName > ''' + ISNULL((SELECT MAX(EmpCode) FROM #ADUsers), '') + '''
         ORDER BY sAMAccountName
         PAGE SIZE ' + CAST(@PageSize AS VARCHAR) + '')
    WHERE sAMAccountName NOT IN (SELECT code FROM tbl_usermaster);

    -- 当拉取行数不足一页时，说明已取完所有数据
    IF @@ROWCOUNT < @PageSize
        BREAK;
END

-- 查看最终结果
SELECT * FROM #ADUsers;

DROP TABLE #ADUsers;

方法2：调整AD服务器的查询返回限制（需AD管理员权限） #

如果拥有AD服务器的管理权限，可以修改AD的默认查询返回上限：

• 打开ADSI编辑器，连接到默认命名上下文
• 定位到CN=Default Query Policy,CN=Query-Policies,CN=Directory Service,CN=Windows NT,CN=Services,CN=Configuration,DC=domain,DC=local
• 修改MaxPageSize属性的值（例如改为10000）
• 重启AD服务使设置生效

注意：该修改会影响所有AD查询，需评估对AD服务器性能的影响。

方法3：按子OU拆分查询 #

如果AD用户分布在多个子OU中，可以逐个查询每个子OU，降低单次查询的返回数量：

-- 查询子OU1的用户
SELECT 
    UPPER(sAMAccountName)[EmpCode], 
    givenName [FirstName],
    ISNULL(sn, '') [LastName],
    ISNULL(mail, 'NA') [Email],
    telephoneNumber [Telephone], 
    cn [DisplayName],
    Mobile, 
    title [Designation],
    CASE 
        WHEN UserAccountControl = 512 THEN 'Active' 
        WHEN UserAccountControl = 514 THEN 'In-Active' 
        WHEN UserAccountControl = 66048 THEN 'Active, password never expires' 
        WHEN UserAccountControl = 66050 THEN 'In-Active, password never expires'  
        ELSE 'NA' 
    END [Status],
    UserAccountControl,
    UPPER(ISNULL(physicalDeliveryOfficeName, 'NA')) [Office]
FROM OPENQUERY(ADSI, 
    'SELECT sAMAccountName, givenName, sn, mail, telephoneNumber, cn, Mobile, title, UserAccountControl, physicalDeliveryOfficeName
     FROM ''LDAP://domain.local/OU=SubOU1,OU=Company,DC=domain,DC=local'' where objectClass = ''User'' ')
WHERE sAMAccountName NOT IN (SELECT code FROM tbl_usermaster);

-- 查询子OU2的用户，以此类推
SELECT 
    UPPER(sAMAccountName)[EmpCode], 
    givenName [FirstName],
    ISNULL(sn, '') [LastName],
    ISNULL(mail, 'NA') [Email],
    telephoneNumber [Telephone], 
    cn [DisplayName],
    Mobile, 
    title [Designation],
    CASE 
        WHEN UserAccountControl = 512 THEN 'Active' 
        WHEN UserAccountControl = 514 THEN 'In-Active' 
        WHEN UserAccountControl = 66048 THEN 'Active, password never expires' 
        WHEN UserAccountControl = 66050 THEN 'In-Active, password never expires'  
        ELSE 'NA' 
    END [Status],
    UserAccountControl,
    UPPER(ISNULL(physicalDeliveryOfficeName, 'NA')) [Office]
FROM OPENQUERY(ADSI, 
    'SELECT sAMAccountName, givenName, sn, mail, telephoneNumber, cn, Mobile, title, UserAccountControl, physicalDeliveryOfficeName
     FROM ''LDAP://domain.local/OU=SubOU2,OU=Company,DC=domain,DC=local'' where objectClass = ''User'' ')
WHERE sAMAccountName NOT IN (SELECT code FROM tbl_usermaster);

内容的提问来源于stack exchange，提问作者spruless