Ever have to pull data from something using OpenQuery?  Ever run up against the 1000 (in our case 901) returned record limit?  Ever looked for solutions on the internet?

We were recently commissioned by our auditing group (nice way of saying we were dinged) to be able to report on who had what rights to what databases. We would need to be able to do this quarterly.  This set everyone off, running around like chickens with their heads cut off.  We decided to be proactive and made the decision to move everyone to security assigned only by Active Directory groups.  The groups would either be a department or describe a specific function.  Further, we decided to automate the quarterly process so that the manager would be sent an email from Reporting Services specifying the current rights for the members of their team, asking whether they still needed those rights.

In order to do this we created a linked server to Active Directory and executed an LDAP query using OpenQuery.  After a bit of tweaking, we ran the query and…..  As you expected, it failed.  Hmmmm must be we have more than 901 users.  In the end it turns out that we had more than 50,000.

So the problem is how to control how many users are returned with each query without knowing the data counts before.  Indeed, you don’t know how the data will spread out across the spectrum you are querying.  We scoured the internet.  We found this question posed many times, but no pointers toward answers.

Being an old-school programmer, the following solution eventually suggested itself; loop through the letters of the alphabet and pull by last name.  We rewrote the SQL.  We ran it and…. Yup, it failed.  Evidently there were more than 901 users with last names that began with B for instance. 

Okay, let’s try an inner and an outer loop.  The inner loop will look at the first initial in the first name and the outer loop will look at the first letter of the last name.  We rewrote it.  We ran it and….. failed again.  You guessed it.  Some combinations had more than 901 user with this combination.

So we added a third and final loop.  This time we had the outer loop for the first letter of the last name, a middle loop for the second letter of the last name, and an inner loop for the first letter of the first name.  We ran it and…… worked like a charm.

There are a couple of caveats.  First, you have to create a table to hold the results to insert it into.  Otherwise there is no way to get what you want from the exec( @cmdTxt ) command.   Second, this may not be speedy.  It took 10 minutes to execute this query, to return all the results.  It is running 26^3 queries against Active Directory using a linked server.  That’s 17,576 queries.  For us, that was okay; we will only run this once a quarter.  Third, it took some work to get the linked server set up correctly.

When all is said and done, this is a really good solution for us.  It allows us to automate and transparently handle the database rights that our auditing department requires.  Hoping you find this useful.