Staff member user account creation script

Powershell, Windows

Sharing is caring, right? He is a powershell script I wrote that creates new users based on their First name and surname, checks to see if a username exists from their first name in AD, if it does append the first letter from their surname and use that as their username, if that exists, append the next letter, etc. I also have logic to set and create the user’s home drive, add the account to the correct security groups, create a remote mailbox, begin a delta sync on Azure AD Connect, log to a text file for audit purposes and print off a word document to pass on details to new user.

#New Staff Account Creation Script, Created by NCK - Last Revised 14/05/2018


$Colours = "Orange", "Green", "Yellow", "Purple"

$RandomColour = Get-Random $Colours

$RandomNumber = Get-Random -Minimum 100 -Maximum 999

$FirstPassword = -join ($RandomColour,"00",$RandomNumber)

Write-Host "**********************************************************"

Write-Host "New Staff Login Creation Script"

Write-Host "Please follow the instructions as follows:"

Write-Host "**********************************************************"

$First =Read-Host "Enter First Name"

$Last =Read-Host "Enter Surname"

$Initials=Read-Host "Enter Initials (First Letter of First Name and First and Last Letter of Surname"

$Title =Read-Host "Job Title (Optional)"

$StaffType=Read-Host "Make an Admin Staff (type 1),Teaching Staff (type 2), Governors (type 3), Invigilators (Type 4)"

$AccountExpiryDate=Read-Host "Enter Expiry Date"

$HomeDriveLetter = "F:"

$SamAccountName = $First + $Last.Substring(0,0).ToLower()

$Filter = $First + '*'

$SimilarUsers = @( Get-ADUser -Filter {proxyAddresses -Like $Filter} -ErrorAction SilentlyContinue | Select-Object SamAccountName )

#do an if statement to check if username already exists in AD, if it does do an else and add a character to the surname e.g "Bob Smith" Would be bobs and if "bobs" exists it'll be "bobsm"

if ($SamAccountName -notin $SimilarUsers.proxyAddresses + "") {

#return $SamAccountName

New-ADUser "$First $Last" -SamAccountName $SamAccountName -GivenName $First -Surname $Last -DisplayName "$First $Last" -Path 'OU=Staff,OU=Staff Users,DC=contoso,DC=local' -PasswordNeverExpires $false -Department $Dept -Title $Title -Company "Carmel College" -EmailAddress $SamAccountName'' -Initials $Initials -UserPrincipalName $SamAccountName''


else {

$i = 1

while ($SamAccountName -in $SimilarUsers.proxyAddresses + "") {

if ($i -eq $Last.Length + 1) {

throw "No Available Usernames."


$SamAccountName = $First + $Last.Substring(0,$i)


#return $SamAccountName

New-ADUser "$First $Last" -SamAccountName $SamAccountName -GivenName $First -Surname $Last -DisplayName "$First $Last" -Path 'OU=Staff,OU=Staff Users,DC=contoso,DC=local' -PasswordNeverExpires $false -Department $Dept -Title $Title -Company "Company Name" -EmailAddress $SamAccountName'' -Initials $Initials -UserPrincipalName $SamAccountName''



Write-Host "a new username named $SamAccountName is about to be created"

$HomeDrivePath = "\\fileserver\UserHomeFolders\$SamAccountName"

#Flick the AD Object to Enabled

#Set the Password on the AD Object to the one that was randomly generated in the #FirstPassword Variable

Start-Sleep -s 5

Set-ADAccountPassword -Identity $SamAccountName -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $FirstPassword -Force) -ErrorAction SilentlyContinue

Enable-ADAccount -Identity $SamAccountName -ErrorAction SilentlyContinue

Set-ADAccountExpiration -Identity $SamAccountName -DateTime $AccountExpiryDate

Start-Sleep -s 5

#Set the HomeDrive Letter and HomeDirectory attributes on the AD Object

Set-ADUser -Identity $SamAccountName -HomeDrive $HomeDriveLetter -HomeDirectory $HomeDrivePath -ChangePasswordAtLogon $true

#Add the User to the Printer Users and Staff_Users Security Groups

Get-ADUser $SamAccountName | Add-ADPrincipalGroupMembership -MemberOf "Print Users"

Get-ADUser $SamAccountName | Add-ADPrincipalGroupMembership -MemberOf "Staff_Users"

#If 1 then add user to the Admin Security Group and move to Admin Staff OU, if 2 add user to Teaching Staff Security Group Group and move to Teaching Staff OU

switch ($StaffType)


'1' {

Get-ADUser $SamAccountName| Move-ADObject -TargetPath 'OU=Admin Staff,OU=Staff,OU=Staff Users,DC=contoso,DC=local'

Get-ADUser $SamAccountName | Add-ADPrincipalGroupMembership -MemberOf Admin


'2' {

Get-ADUser $SamAccountName| Move-ADObject -TargetPath 'OU=Teaching Staff,OU=Staff,OU=Staff Users,DC=contoso,DC=local'

Get-ADUser $SamAccountName | Add-ADPrincipalGroupMembership -MemberOf Teachers


'3' {

Get-ADUser $SamAccountName| Move-ADObject -TargetPath 'OU=Governors,OU=Staff,OU=Staff Users,DC=contoso,DC=local'


'4' {

Get-ADUser $SamAccountName| Move-ADObject -TargetPath 'OU=Invigilators,OU=Staff,OU=Staff Users,DC=contoso,DC=local'



$homeShare = New-Item -path $HomeDrivePath -ItemType Directory -force -ea Stop

$acl = Get-Acl $homeShare

$FileSystemRights = [System.Security.AccessControl.FileSystemRights]"Modify"

$AccessControlType = [System.Security.AccessControl.AccessControlType]::Allow

$InheritanceFlags = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"

$PropagationFlags = [System.Security.AccessControl.PropagationFlags]::None

$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule ($SamAccountName, $FileSystemRights, $InheritanceFlags, $PropagationFlags, $AccessControlType)


Set-Acl -Path $homeShare -AclObject $acl -ea Stop

$ExchangeServer = "mailserver"

# Import session for Exchange and Create a Remote Mailbox (New Staff are in O365)

$SO = New-PSSessionOption -SkipCACheck -SkipRevocationCheck -SkipCNCheck

$ExchSession = New-PSSession -ConfigurationName Microsoft.Exchange -SessionOption $SO -ConnectionUri http://$ExchangeServer/powershell -Authentication Kerberos -AllowRedirection

Import-PSSession $ExchSession

Enable-RemoteMailbox $SamAccountName -RemoteRoutingAddress $SamAccountName''

$AADConnectSvr = "AADConnectServer"

# Import session for Azure AD Connect and Run a Delta Sync

$AADsession=New-PSSession -ComputerName $AADConnectSvr

#Import-PSSession $AADsession

Invoke-Command -Session $AADsession {

Start-ADSyncSyncCycle -PolicyType Delta


#Logging - Audit Purposes - Log what computer made the account, what logged in user who ran the script. "User created by: $LoggedInUser on $ComputerName for: $First $Last $SamAccountName $FirstPassword



$NewLine = -join ("`r`n$(get-date -f yyyy-MM-dd-ss) - A New Staff Account was created by $env:UserName on $env:ComputerName for $First $Last - username is $SamAccountName and Password is $FirstPassword")

Add-Content -Value $NewLine -Path "$LogFilePath$LogFileName"

Write-Host $NewLine

$word=new-object -ComObject "Word.Application"





$selection.TypeText(("A new staff user account has been made for $First $Last"))


$selection.TypeText(("Username: $SamAccountName"))


$selection.TypeText(("Password: $FirstPassword"))


$selection.TypeText(("Your email address will be [email protected]"))


$selection.TypeText(("When you first login you will be asked to reset your password. Your password must be at least 8 characters long and MUST contain at least one capital letter and at least one number"))


Can’t connect to SQL instance using Windows Authentication

Can’t connect to SQL instance using Windows Authentication


I had an odd issue at work where I could not connect to a SQL server using Windows authentication even though I had the authentication set to mixed mode. I was able to logon with the SQL authentication details but not with Windows authentication. I was presented with the following message – “The target principal name is incorrect. Cannot generate SSPI context.” When looking into this it turned out that the SPNs were registered to a user object that did not run the service on the host of where the SQL Server was running. I use service accounts for SQL server, this one named SCCM-SQLSRV and this did not have the SPNs registered to it.

To find out the user object/computer object that the SPNs are registered to you must first run the following command. Replacing $Computername with the hostname of your server and $FQDN with your fully qualified domain name.

setspn -Q MSSQLSvc/$Computername.$FQDN

This will bring back which computer object/user object the SPN belongs to like so:

Checking domain DC=contoso,DC=com

CN=AccountThatHostsTheSPNs,CN=Service Accounts,DC=contoso,DC=com

From this, we see that the SPN is registered to a hypothetical user object named “AccountThatHostsTheSPNs”.

What we must now do is unregister the SPN. For this instead of using -Q we use -D.

Now we must register the SPN, again using the setspn command or if you really wanted to you could go to the user object in AD and go into attributes editor and do it that way but for the purpose of this guide, I will stick to using setspn. So this time we use the A parameter and append the user object to the end of the command to which you would like it registered to. If you’re using local system on the SQL service then this would be the computer object. If this is a user object, you must also make sure that the user object has AD permissions on the computer object of the SQL server to read and write Service Principal Names.

setspn -A MSSQLSvc/$Computername.$FQDN $UserObjectYouWantToRegisterTheSPNTo

You test that this works by issuing a kerberos ticket for this SPN, I did this initally which pointed me in the right direction as to what was wrong. You can also test and find out which user object it should be registered to this way.

Previous to doing this fix, when I issued a kerberos ticket, I would get the following critical warning in the event log. Which pointed me to the solution to why this error was coming up and I could not authenticate.

The Kerberos client received a KRGB_AP_ERR_MODIFIED error from the server SCCM-SQLSRV. the target name used was MSSSQLSvc/ This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target principal name (SPN) is registered on an account other than the account the target service is running. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (contoso.local) is different from the client domain (contoso.local), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

Remember to restart the SQL service once you’ve updated the SPNs. It should then work. I hope this helps somebody as I was coming up blind trying to google this one.