Load all AD User in SqlServer with smo

This script loads all selected (NT user attributes) in a Sql Server Db Table for each Domain in your running forest.

To simplify script sharing I didn’t sign the script.

Script requirements:

Powersmo - Dan Sullivan

Powershell Comunity Extension 1.1.1

SqlServer whith SMO – locally installed

Script output:

Sql Server: local

Database: TempDb

Table: AdPerson (populated with all Ad User attributes)

=====================================================================================

Set-ExecutionPolicy Unrestricted

c:\temp\InitPowerSMO.ps1

$server = SMO_Server

$db = SMO_Database $server "tempdb"

# ----- Add here all AD person attribute to load in the database table ------------------------------

$variabili='Samaccountname','Mail','sn','TelephoneNumber','GivenName','ExtensionAttribute1','Mobile','proxyAddresses','distinguishedName','PhysicalDeliveryOfficeName','description','displayname','Homemdb','MSExchHomeServerName'

# ----- Generate a dynamic query to create table AdPerson ------

$query="CREATE TABLE AdPerson ("

$variabili %{ $query=$query+$($_)+' varchar(500), '}

$query=$($query+')').replace("), )","))")

# ----- drop and recreate table AdPerson -----

$DB.ExecuteNonQuery("drop table AdPerson")

$DB.ExecuteNonQuery($query)

#------ Dynamic retrieve DC name for each Ad domain ---------

$dom=Get-WmiObject Win32_NTDomain

$domall=$($dom select DomainName, DomainControllerName, DnsForestName where {$_.DomainControllerName -gt ''} %{$foresta=$_.DnsForestName; $domfo=$foresta.split('.');$domfo1=$domfo[0];$DomainName=$_.DomainName;$root=$_.DomainControllerName.replace('\\','')+'.'+$_.DnsForestName; if($DomainName -ne $domfo1.ToUpper()) {$root=$_.DomainControllerName.replace('\\','')+'.'+$DomainName+'.'+$_.DnsForestName;} $root})

#------- dynamic creation of insert-person function ------

$all='function insert-person (';

$varall=''; $variabili %{$varall=$varall+'$'+$_+','};

$all=$($all+$varall+')').replace(",)",") ");

$all=$all+"{ "" insert into AdPerson values ("

$num=0..($variabili.count-1); $num %{$all=$all+"'{"+$_+"}',"; };

$all=$($all+')').replace(",)",") ") + """ -f "+$varall.substring(0,$($varall.length-1)) + " }";

Invoke-Expression $($all)

write-host $all

# ------- generate code to insert all attributes dynamcally ----

$varall='insert-person'

$REP=@'

.REPLACE("'","''")

'@

$variabili %{ $varall=$varall+' $($_.'+$_+'.tostring())'+$REP}

# ------ for each DC - for each user -insert all selected attributes in DB table TempDb.Adperson

$domall %{$users=get-adobject -server $_ -class "User" -outvariable users -PageSize 1000; $users %{Invoke-Expression $($VARALL)} %{$DB.ExecuteNonQuery($_)} }