Learn about Windows PowerShell
Hey, Scripting Guy! I’m trying to pull a list of all my users out of Active Directory. I’m using ADO to search for these users, but no matter what I do I can only get 1,000 user names, even though we probably have 10 times that many user accounts. What could I be doing wrong?-- MC, Athens, GA
Hey, MC. Boy, if we had a nickel for every time we’ve been asked this question we’d have - well, OK, we’d have a nickel. But that’s only because we just started doing this column; trust us, this is a question that gets asked all the time.
What you’re running into here is a default built into Active Directory: by default, any time you run a query against Active Directory you only get back the first 1,000 objects. That’s it. It doesn’t matter how many objects you actually have in Active Directory, it doesn’t matter how much memory you have on your domain controllers, nothing matters except this: you only get back the first 1,000 objects.
Fortunately, though there’s an easy workaround. Here’s a script that returns all the user accounts in Active Directory. Yes, we said all; if you have 100,000 user accounts, this script will return all of them:
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' " & _
Set objRecordSet = objCommand.Execute
Do Until objRecordSet.EOF
So what’s the secret? The secret is the line that sets the Page Size to 1000. What’s so special about that? Well, if you don’t specify a Page Size, Active Directory returns only the first 1,000 items. If you do specify a Page Size, however, Active Directory will return the first x items, then pause for a split second and return the next x items, then pause for a split second, and so on. This will continue until all the items have been returned. In this example, we set the Page Size to 1,000 (the maximum), so we’ll get the first 1,000 items, there will be a (typically imperceptible) pause, then we’ll get the next 1,000 items. That will continue until all the items have been returned.
For more information about using scripts to search Active Directory, see this previously-recorded Scripting Guys' webcast.
you saved me , thank you very much for the amazing fix.
It's nice to see that this has a simple explanation AND a simple fix. I just ran into this 1,000 object limit and it took just a few minutes of searching to find this page. My initial search took me to www.rlmueller.net/ADOAlternatives.htm which pointed me in the right direction and my second search landed me here.