kSQL 

CREATE TABLE #ADRole(roleName VARCHAR(50);

     INSERT INTO #ADRole(roleName)

     VALUES ('QA','DBAUsers');

CREATE TABLE #Users(samAccountName VARCHAR(256));


DECLARE @LoopID INT = 1;   

DECLARE @ADRole VARCHAR(200) = (SELECT roleName FROM #ADRole WHERE ID = @LoopID);       

DECLARE @MaxLoopID INT = (SELECT COUNT(0) FROM #ADRole) + 1;

DECLARE @SQL VARCHAR(1000) = 'powershell.exe c:\ADSearch_GetUsersByRole.ps1 ''CurrentRoleToProcess''';     

SET @SQL = REPLACE(@SQL, 'CurrentRoleToProcess', @ADRole);


WHILE @LoopID < @MaxLoopID           

 BEGIN;      

     INSERT INTO #Users(samAccountName)      

     EXEC xp_cmdshell @SQL;          


     SET @LoopID = @LoopID + 1;                

     SET @ADRole = (SELECT roleName FROM #ADRole WHERE ID = @LoopID);                            

     SET @SQL = 'powershell.exe c:\ADSearch_GetUsersByRole.ps1 ''CurrentRoleToProcess''';         

     SET @SQL = REPLACE(@SQL, 'CurrentRoleToProcess', @ADRole);              

 END;        

DELETE #Users WHERE samAccountName IS NULL OR samAccountName = 'The system cannot find the file specified.' -- $objUserSearcher returns one NULL row after each array; also, delete rows where you may have searched for an invalid role name

As you can imagine, I wanted to spin through several roles and grab the sAMAccountName of the members. Here's a little SQL script to help you do that!

Querying AD with Powershell

As you may remember from my earlier post on Querying Active Directory via a Linked Server, one of the biggest limitations is that a linked server is unable to return an array, only a singular attribute of an AD object. In order to get around this issue I resorted to using Windows PowerShell (ps) to query AD. There are likely other ways to do this, but when all you have is a hammer ...


The first thing I did was get PowerShell up and running on my local machine so I could test my scripts. Seasoned ps users can skip this section. Windows Powershell is located in the Windows Start menu. Once you open it, you'll want to change the execution policy to allow scripts to run. You do this by typing in the following:

set-executionpolicy remotesigned


At the prompt choose Yes and hit Enter. There are three different access levels and you shoul consult the help file to read more about the other options. I chose the one that would allow me to cause the least amount of damage yet still run my scripts. Once this is finished, navigate to your c drive using the following command and prepare to execute the scripts you saved within!

cd c:\


Wait, what script? I skipped that part! Here it is: I cobbled up a short ps script that accepts a string parameter (in this case it's Role), iterates through objects in the specified AD path (in this case it the objects are users), and returns the Common Name (cn) attribute of each object. My example assumes I have AD path LDAP://OU=Users,DC=kSQL,DC=net. What you end up with is a list (array) of user sAMAccountNames that members of the specified AD role. An example role might be QA or DBUsers. Click here to download the script.