Use PowerShell to Create Active Directory User Accounts from Data Contained in the Adventure Works 2012 Database

This past Saturday, I presented a session at PowerShell Saturday 003 in Atlanta. Towards the end of the presentation, I created 290 Active Directory user accounts by using the information for employees contained in the Adventure Works 2012 database. This is actually a PowerShell script that I whipped up Friday night at the hotel after the speaker dinner. I populated some demographic information by joining multiple tables together from that particular database. There is more demographic information to be had, but I got tired of joining tables and thought this was good enough for a proof of concept.

I’m using implicit remoting to keep from having to install the Active Directory tools or SQL tools on the Windows 8 computer that this script is being run from.

Import-PSSession -Session (New-PSSession -ComputerName dc01) -Module ActiveDirectory
Import-PSSession -Session (New-PSSession -ComputerName sql01) -Module SQLPS

Invoke-Sqlcmd -ServerInstance sql01 -Database AdventureWorks2012 -Query `
"select Employee.LoginID, Person.FirstName as givenname, Person.LastName as surname,
Employee.JobTitle as title, Address.AddressLine1 as streetaddress, Address.City,
Address.PostalCode, PersonPhone.PhoneNumber as officephone
from HumanResources.Employee
join Person.Person
on Employee.BusinessEntityID = Person.BusinessEntityID
join Person.PersonPhone
on Person.BusinessEntityID = PersonPhone.BusinessEntityID
join Person.BusinessEntityAddress
on PersonPhone.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
join Person.Address
on BusinessEntityAddress.AddressID = Address.AddressID" |

select @{l='Name';e={$_.givenname+" "+$_.surname}},
@{l='DisplayName';e={$_.givenname+" "+$_.surname}},
title, givenname, surname, officephone, streetaddress, postalcode, city |

New-ADUser -Path "OU=AdventureWorks Users,OU=Users,OU=Test,DC=mikefrobbins,DC=com" -PassThru |
select Name, SamAccountName, UserPrincipalName | Format-Table -AutoSize


The amazing thing is the entire process including creating the PSSessions only takes 23 seconds to create all 290 Active Directory user accounts:


If the PSSessions are created first, it only takes 5 seconds to create all 290 user accounts:


Except for creating the PSSessions, this is all a PowerShell one liner! The crazy insane thing is these VM’s are running on an older single CPU system with an E6600 CPU and 8GB of RAM, although all of the servers are running server core with no GUI (low overhead :-)).