Solved

Powershell - Create Active Directory Users From SQL

Posted on 2013-12-05
1
372 Views
Last Modified: 2014-01-29
I need to write a Powershell script to perform the following steps.

1. Pull user information from two separate tables in a SQL database (database1)
    a. Pull FirstName, LastName, Nickname, and GovernmentID fields from Table1
    b. Pull LastTerm and CampusName from Table2
    Note: Table1 and Table2 are related via an EmployeeID field that is the PK in both    tables

2. Create new AD username by concatenating the "FirstName" + "." + "LastName" fields.  
3. Create new AD passwords for each user by concatenating the first 4 characters of "FirstName" + "." + "LastName" + "." + "GovernmentID".


Additional requirements:
1. Only create accounts if an account with the same username does not already exist in AD.
2. Only create accounts if the LastTerm field is less than 1 year ago (measured in standard years)
3. If two accounts exist in SQL that have the same FirstName.LastName, but different EmployeeID's, I need to substitute the Nickname for the FirstName in both username and password generators, using the same First 4 rule in the password
4. Create each account in an OU that bears the name of the CampusName field


What would a template for this type of script look like?
0
Comment
Question by:marrj
1 Comment
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 39701417
$conn.ConnectionString = "Server=ServerName;Database=DatabaseName;User Id=user;Password=pwd;"

$sql = "SELECT t1.FirstName, t1.LastName, t1.FirstName " '.' + t1.LastName As [UserName],  t1.Nickname, t1.GovernmentID, t2.LastTerm, t2.CampusName FROM Table1 as t1 
INNER JOIN Table2 as t2 ON t1.EmployeeID=t2.EmployeeID"
$conn.Open()

$cmd = New-Object Data.SqlClient.SqlCommand($sql, $conn)
$Reader = $cmd.ExecuteReader()

$dt = New-Object System.Data.DataTable 
$dt.Load($Reader)  

#loop through each row of the datatable
$dt.Rows | foreach {
	#for each username check if it already exists
	$User = Get-ADUser -LDAPFilter "(sAMAccountName=$_.UserName)"
 	if ($User -eq $null) {
		#user does not exist
		#check if LastTerm is less than a year ago
		$datediff = new-TimeSpan $_.LastTerm $(Get-Date);
		if ($datediff -lt 365){
			New-ADUser -Name $_.UserName
		}
		
	}
 
}

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now