При использовании связанного сервера к AD, может возникнуть проблема получения всех записей из AD.
Поскольку есть ограничения по количеству строк для запроса к LDAP .
Windows Server 2008 and newer domain controller returns only 5000 values in a LDAP response
Самый простой вариант разбить запрос на несколько частей по любому признаку, самое логичное по sAMAccountname
Таким образом один запрос превращается в два, три и тд.
SELECT NAME,
givenName,
SN,
sAMAccountname,
mail,
company,
division,
department,
manager,
userAccountControl,
ObjectGuid,
CASE
WHEN ad.accountexpires IN ('9223372036854775807', '0', '129895740000000000') THEN
'9999-12-31'
ELSE DATEADD(
mi,
(CAST(ad.accountExpires AS BIGINT) / 600000000)
+ DATEDIFF(Minute, GETUTCDATE(), GETDATE()),
CAST('1/1/1601' AS DATETIME2)
)
END AS AccountExpiresDate
FROM OPENQUERY(
AD_SERVER,
'select Name, givenName, SN, sAMAccountname, mail, company,division,department,manager, userAccountControl,ObjectGuid,accountExpires from ''LDAP://DC=domen,DC=ru'' WHERE sAMAccountname < ''L'' and objectClass=''user'''
) AS AD
SELECT NAME,
givenName,
SN,
sAMAccountname,
mail,
company,
division,
department,
manager,
userAccountControl,
ObjectGuid,
CASE
WHEN ad.accountexpires IN ('9223372036854775807', '0', '129895740000000000') THEN
'9999-12-31'
ELSE DATEADD(
mi,
(CAST(ad.accountExpires AS BIGINT) / 600000000)
+ DATEDIFF(Minute, GETUTCDATE(), GETDATE()),
CAST('1/1/1601' AS DATETIME2)
)
END AS AccountExpiresDate
FROM OPENQUERY(
AD_SERVER,
'select Name, givenName, SN, sAMAccountname, mail, company,division,department,manager, userAccountControl,ObjectGuid,accountExpires from ''LDAP://DC=domen,DC=ru'' WHERE sAMAccountname > ''L'' and objectClass=''user'''
) AS AD
Расшифровка поля userAccountControl
'AcctCtrl' = ( CASE WHEN userAccountControl = 2 THEN 'Account is Disabled'
WHEN userAccountControl = 16 THEN 'Account Locked Out'
WHEN userAccountControl = 17
THEN CONVERT (VARCHAR(48), 'Entered Bad Password')
WHEN userAccountControl = 32
THEN CONVERT (VARCHAR(48), 'No Password is Required')
WHEN userAccountControl = 64
THEN CONVERT (VARCHAR(48), 'Password CANNOT Change')
WHEN userAccountControl = 512 THEN 'Normal'
WHEN userAccountControl = 514 THEN 'Disabled Account'
WHEN userAccountControl = 8192
THEN 'Server Trusted Account for Delegation'
WHEN userAccountControl = 524288
THEN 'Trusted Account for Delegation'
WHEN userAccountControl = 590336
THEN 'Enabled, User Cannot Change Password, Password Never Expires'
WHEN userAccountControl = 65536
THEN CONVERT (VARCHAR(48), 'Account will Never Expire')
WHEN userAccountControl = 66048
THEN 'Enabled and Does NOT expire Paswword'
WHEN userAccountControl = 66050
THEN 'Normal Account, Password will not expire and Currently Disabled'
WHEN userAccountControl = 66064
THEN 'Account Enabled, Password does not expire, currently Locked out'
WHEN userAccountControl = 8388608
THEN CONVERT (VARCHAR(48), 'Password has Expired')
ELSE CONVERT (VARCHAR(248), userAccountControl)
END )
