PowerShell, Technology

Powershell: Automating Exiting Users



I have being playing around with Powershell a lot more lately and trying to learn and utilise it as much as possible. One of the repetitive tasks we deal with is when a student exits the school. There are a number of steps in AD that have to be undertaken each time a student leaves. These include:

  • Disable the account
  • Move the account to a ‘Disabled’ ou
  • Remove the account from all Groups except ‘Domain Users’
  • Hide the user in the Exchange Address List

I knew it would be pretty easy to get a Powershell script to carry out these tasks, the tricky bit was going to be getting it automated so I have to do a as little as possible. I could enter the usernames that were to be disabled, but even better would be to get a scheduled task going that would check for users to be disabled and then carry out the tasks.

Our school uses the SIMON system to manage students. When a student is leaving the administration staff update this in SIMON and IT get an email notifying us to disable the accounts. SIMON has an MS SQL back-end so I figured if I could pull the information on the students who were leaving, I could pass this to my Powershell script to process their accounts.

High Level Steps

  1. Powershell script runs which queries the SQL database to see if any students are flagged to have exited in the last week. If there is, it outputs the details to a CSV.
  2. Powershell script imports this CSV, checks the usernames to see if the accounts are enabled
  3. If they are enabled it disables the account, moves it to a different OU etc…
  4. Script updates a log to say which accounts have been disabled and when
  5. Sends an email with the details of the accounts that have been disabled

Powershell Code

For ease of reading I have broken it up into a couple of sections

Querying the SQL Database

This section creates the connection to the SQL server, sends the query, then if there are results it outputs to a CSV file with that days date as the name.


# Source SQL Server
$SQLServerName =

# Source SQL Database
$SQLDatabase =

# SQLQuery
$SQLQuery1 = “SELECT blah blah'”

# Source SQL Username
$SQLUsername =

# Source SQL Password
$SQLPassword =

# SQL Server Connection Settigs
$SQLConnectionString = “Server=$SQLServer;uid=$SQLUsername; pwd=$SQLPassword;Database=$SQLDatabase;Integrated Security=False;”
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $SQLConnectionString

# Issue SQL Connection Commands for Query1
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery1
$SQLCommand.Connection = $SQLConnection

# Pull data
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
$SQLDataSet = New-Object System.Data.DataSet

$SQLQuery1DataSet = $SQLDataSet.Tables[0]

# Export to CSV
$SQLQuery1DataSet | Export-Csv $TargetStudentCSV -NoTypeInformation

Process Exiting Students

This section:

  • imports the CSV with todays date (if it exists)
  • loops through the CSV and first checks if the account is enabled
  • then hides it from the address list, removes it from groups, moves it to a ‘Disabled OU’, disables the account


#Set the variables to be used

$CurrentYear = (Get-Date).ToString(‘yyyy’)
$TodaysDate = (Get-Date).ToString(‘yyyy-MM-dd’)
$DisabledOU =

$StudentCSV =
# Exchange Mailbox Server UNC
$ExchangeServerUNC = “\\$ExchangeServer\”
# Exchange Mailbox Server Powershell Path
$ExchangeServerPath = “http://” + $ExchangeServer + “/powershell”
# Connect to Exchange Server
$s=New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $ExchangeServerPath -Authentication Kerberos
Import-PSSession -Session $s -ErrorAction:SilentlyContinue
# ======================================
# ******** Import list and PRocess users ********
# ======================================
# Import Exiting Students CSV
$Students = Import-Csv -Path $StudentCSV
ForEach ($User in $Students)
$StudentCode = $User.UID
#Check if the student has already been processed by check if the account is disabled
$AlreadyProcessed = Get-ADUser $StudentCode | %{$_.enabled}
if ( $AlreadyProcessed -eq $false)
$Output = “User ” + $StudentCode + ” is already disabled”
Write-Output $Output
# hide from Addrress List
get-mailbox $StudentCode | set-mailbox -HiddenFromAddressListsEnabled $true
#Remove all groups except ‘Domain USers’
$RemoveGroups = Get-ADPrincipalGroupMembership -Identity $StudentCode | where {$_.Name -ne “Domain Users”}
Remove-ADPrincipalGroupMembership -Identity $StudentCode -MemberOf $RemoveGroups -Confirm:$false
#Move to diabled OU
Get-ADUser $StudentCode | Move-ADObject -TargetPath $DisabledOU
#Disable Account
Disable-ADAccount $StudentCode

Update The Exiting Students Log file

This section exports the name of the account disabled and todays date to CSV log file.
$LogFile =
$LogDetails = New-Object PSObject -Property @{ Details = $StudentCode + ” has been disabled”; Date = $TodaysDate}
Export-Csv -InputObject $LogDetails -Append -path $LogFile -NoTypeInformation

Send email of log

This section sends an email with the log of which accounts have been disabled that day.
#Email variables
$To = @(“you@yourcompany.com”)
$From = “no-repy@yourcompany.com”
$SMTPServer =
#Import details from log matching todays date
$Body = Import-Csv -Path $LogFile | Where-Object {$_.Date -eq $TodaysDate}
#If contains data, send the email
$Body = Import-Csv -Path $LogFile | Where-Object {$_.Date -eq $TodaysDate} | ConvertTo-Html | Out-String
Send-MailMessage -to $To -subject “Disabled Student Accounts Log” -Body $Body -BodyAsHtml -SmtpServer $smtpserver -From $from


This has worked quite well so far. I know my code could do with some cleaning up but I am refining it as I learn.

Leave a Reply

Your email address will not be published.