Assume you have a database with all the users, which is modified by your HR department.
Once they are added into system, you need prepare accounts for them. Here is my way to automatically add/delete users to system.
Considering the data is different from place to place. Please change the related field accordingly.
We will have 2 servers related,

Preparation:
1, the database server, we will use <db1> to name it, if your database server name is SQLDB01, please replace <db1>with it. And the database we use here as example is <dbExample>. We need prepare a domain account for the automation export, here we can go to dc and create a user <exporter>. Make sure exporter have all the access to the database to export as well as read/write access to the folders on database server and on dc.
2. The active directory Domain controller <dc1>.
Steps 1- Fetch data.
1. Go to the database server and create scripts to export the data to your server. Create a folder under D drive called dataExport, create a batch called dataExport.bat. This is the scripts of dataExport.bat:

Rem dataExport.bat
del *.csv
sqlcmd -S <db1> -d <dbExample> -E -i d:\dataExport\setnocounton.sql,d:\dataExport\exportUsers.sql -o "d:\dataExport\students.csv" -h-1 -s"," -w 700
sqlcmd -S dataServer -d DataBase -E -i d:\dataExport\setnocounton.sql,d:\dataExport\exportUsersExit.sql -o "d:\dataExport\studentsExit.csv" -h-1 -s"," -w 700
copy d:\dataExport\*.csv \\dc1\data$\ /y
exportUsers.sql
SELECT [STKEY],[SURNAME],[FIRST_NAME],[BIRTHDATE],[ENTRY],[STATUS],[SCHOOL_YEAR],[HOME_GROUP] FROM [DataBase].[dbo].[ST]   Where ENTRY >  DATEADD(day,-8,GETDATE()) and HOME_GROUP<>'ZZZ' and STATUS = 'ACTV'
exportUsersExit.sql
SELECT [STKEY],[SURNAME],[FIRST_NAME],[BIRTHDATE],[ENTRY],[STATUS],[SCHOOL_YEAR],[HOME_GROUP],[EXIT_DATE] FROM [DataBase].[dbo].[ST]  Where EXIT_DATE >  DATEADD(day,-17,GETDATE()) and STATUS = 'LEFT'

So the batch will export all the data to dc01.   Students.csv give us all the users will need be created, who are started within 8 days from the today. studentExit.csv give us the users will need be deleted, since their status marked as ‘LEFT’.

2. Go to scheduled task and create a task to run this batch every day, make sure you choose ‘run at ’ at its original actual folder. Run the task manually by right click and hit run to check if the destination folder have the 2 files we created. Step2, Create/Delete users

 

Const ForReading = 1
Const ForWriting = 2
dim stID,LN,FN,DB,ED,Status,Yr,HG,PW,dd,mm,yy,result,line
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objTextFile = objFSO.OpenTextFile("\\myServer\myData$\students.csv", ForReading)
 Set objLogFile = objFSO.OpenTextFile("\\myServer\myData$\created.log", ForReading)
 Set objTempFile = objFSO.CreateTextFile("\\myServer\myData$\temp.log", ForWriting)
 Do Until objLogFile.AtEndOfStream
 objTempFile.WriteLine(objLogFile.Readline())
 Loop

'data process and manupulation.So we can use data directly. Do Until objTextFile.AtEndOfStream strNextLine = objTextFile.Readline arrValues = Split(strNextLine , ",") stID=UCase(Trim(arrValues(0))) LN=Trim(arrValues(1)) FN=Trim(arrValues(2)) DB=Left(Trim(arrValues(3)),10) dd=Right(DB,2) mm=Left(Right(DB,5),2) yy=Right(Left(DB,4),2) PW=dd&mm&yy&"Bsc" ED=Left(Trim(arrValues(4)),10) Status=arrValues(5) Yr=Trim(arrValues(6)) HG=arrValues(7) on error resume next result=CreateUser(stID, PW, FN, LN , Yr,HG) if result=True then objTempFile.WriteLine(stID &","& PW & ","& FN & ","&LN& ","&Yr& ","&HG & ","& date()) end if Loop objTextFile.close() objLogFile.close() objTempFile.close() objFSO.deleteFile("\\myServer\myData$\created.log") objFSO.MoveFile"\\myServer\myData$\temp.log","\\myServer\myData$\created.log" Function findUser(username, domain) Dim con,com 'Create connection and command object Set con = CreateObject("ADODB.Connection") Set com = CreateObject("ADODB.Command") ' — Opening the connection con.Provider = "ADsDSOObject" 'this is the ADSI-OLEDB provider name con.Open "Active Directory Provider" ' Create a command object for this connection Set com.ActiveConnection = con 'Compose a search string com.CommandText = "SELECT * FROM 'LDAP://" & domain & "' WHERE " & _ "objectCategory='Person' AND " & _ "objectClass = 'user' AND " & _ "Name = '" & ESC_Quotes(username) & "'" ' — Execute the query Set rs = com.Execute 'If the number of records is zero, no user with the specified name exists findUser = (rs.RecordCount <> 0) End Function Private Function CreateUser(username, Password, FirstName, Surname, YearLevel, HomeGroup) Dim Usr1,Usr3,servername,Home,group,group1,group2, OUname , domainDN , userDN , domainName , domName , userdesc , status, statement , pcnt_balance , pcnt_limit , account_app_id, netshare dim dom , userOU , grp , usr Dim secDescriptor , dACLt, ACE domainName = "YourDomainName" 'since Yearlevel under 9 only has 1 digit and over 10 will have 2.
If (YearLevel<=9) then YearLevel = right(YearLevel,1) End If If findUser(username, domainName) Then CreateUser = False Exit Function End If 'LC means language center, should be sent to different OU
Select Case HomeGroup Case "LC" userdesc = "LC Student" OUname = "LC" & ",OU=Students" Case Else userdesc = "Student" OUname = "YR" & YearLevel & ",OU=Students" End Select Set dom = GetObject("LDAP://" & domainName) domainDN = dom.Get("DistinguishedName") domName = dom.Get("Name") Set userOU = GetObject("LDAP://OU=" & OUname & "," & domainDN) Home = "\\myServer\" If (HomeGroup = "LC") then Home = Home+"LC$\" else Home = Home + "Year" & YearLevel &"$\" End If Home = Home + username + "" Set usr = userOU.Create("user", "CN=" & Surname & " " & FirstName) usr.Put "samAccountName", username & vbNullChar usr.Put "userPrincipalName", username & "@" & domainDNtoDNS(domainDN) & vbNullChar usr.Put "description", "Year " & YearLevel & " 2012" & vbNullChar usr.Put "homeDrive", "U:" & vbNullChar usr.Put "homeDirectory", "\\myServer\" & username & "$" & vbNullChar 'usr.put "userAccountControl", UF_NORMAL_ACCOUNT Or UF_PASSWD_CANT_CHANGE Or UF_DONT_EXPIRE_PASSWD usr.Put "displayName", Surname & " " & FirstName & vbNullChar usr.Put "sn", Surname & vbNullChar usr.Put "givenName", FirstName & vbNullChar usr.Put "telephoneNumber", "00000000" & vbNullChar usr.Put "postalCode", "0000" & vbNullChar usr.Put "st", "State Name" & vbNullChar usr.Put "l", "CityName" & vbNullChar usr.Put "c", "AU" & vbNullChar usr.Put "co", "Australia" & vbNullChar usr.Put "company", "Your Company Name" & vbNullChar usr.Put "streetAddress", "Street Address Name" & vbNullChar usr.Put "homeDirectory", Home & vbNullChar usr.SetInfo Set secDescriptor = usr.Get("ntSecurityDescriptor") Set dACL = secDescriptor.DiscretionaryAcl ' Modify the existing entries. 'For Each ACE In dACL ' If UCase(ACE.ObjectType) = UCase(CHANGE_PASSWORD_GUID) Then ' If ACE.Trustee = "Everyone" Then ' ' Modify the ace type of the entry. ' ACE.AceType = ADS_ACETYPE_ACCESS_DENIED_OBJECT ' End If ' ' If ACE.Trustee = "NT AUTHORITY\SELF" Then ' Modify the ace type of the entry. ' ACE.AceType = ADS_ACETYPE_ACCESS_DENIED_OBJECT ' End If ' End If 'Next usr.Put "ntSecurityDescriptor", Array(secDescriptor) usr.SetInfo usr.SetPassword Password usr.AccountDisabled = False usr.Put "pwdLastSet", 0 usr.SetInfo userDN = usr.Get("distinguishedName") On Error Resume Next MkDir Home 'MkDir Home & "\User Profile" Set objShell = CreateObject("Wscript.Shell") objShell.Run "cmd /c mkdir " & Home group = "Students" 'netshare = Shell("net share " & username & "$=f:\students\grade" & YearLevel & "\" & username & " /GRANT:everyone,FULL") Set grp = GetObject("LDAP://CN=" & group & ",CN=Users," & domainDN) grp.Add "LDAP://" & userDN Select Case HomeGroup Case "LC" group2 = "LC_students,OU=LC,OU=Students" Case else group2 = "Year" & YearLevel & ",OU=Students" end select Set grp2 = GetObject("LDAP://CN=" & group2 &","& domainDN) grp2.Add "LDAP://" & userDN 'Set grp = GetObject("LDAP://CN=" & group2 & ",CN=Users," & domainDN) 'grp.Add "LDAP://" & userDN statement = "DACL " & Chr(34) & "Set" & Chr(34) & ", " & Chr(34) & "FILE://" & Home & "\" & Chr(34) & ", " & Chr(34) & username & ":C,Administrators:F" End Function Function ESC_Quotes(str) Dim i , l Dim c ESC_Quotes = "" l = Len(str) For i = 1 To l c = Mid(str, i, 1) Select Case c Case "'" ESC_Quotes = ESC_Quotes & "''" Case Else ESC_Quotes = ESC_Quotes & c End Select Next End Function Function domainDNtoDNS(domDN) Dim dns Dim i , i2 dns = "" i = 1 While i > 0 i = InStr(i, domDN, "dc=", vbTextCompare) If i > 0 Then i2 = InStr(i, domDN, ",", vbTextCompare) If i2 > 0 Then dns = dns & Mid(domDN, i + 3, i2 - i - 3) & "." Else dns = dns & Mid(domDN, i + 3) End If i = i2 End If Wend domainDNtoDNS = dns End Function Private Function delete(username,YearLevel,HomeGroup) Dim Usr1,Usr3,servername,Home,group,group1,group2, OUname , domainDN , userDN , domainName , domName , userdesc , status, statement , pcnt_balance , pcnt_limit , account_app_id, netshare dim dom , userOU , grp , usr Dim secDescriptor , dACLt, ACE domainName = "YourDomainName" If (YearLevel<=9) then YearLevel = right(YearLevel,1) End If If findUser(username, domainName) Then CreateUser = False Exit Function End If Select Case HomeGroup Case "LC" userdesc = "LC Student" OUname = "LC" & ",OU=Students" Case Else userdesc = "Student" OUname = "YR" & YearLevel & ",OU=Students" End Select Set dom = GetObject("LDAP://" & domainName) domainDN = dom.Get("DistinguishedName") domName = dom.Get("Name") Set userOU = GetObject("LDAP://OU=" & OUname & "," & domainDN) Home = "\\myServer\" If (HomeGroup = "LC") then Home = Home+"Students\LC\" else Home = Home + "Students\Year" & YearLevel &"\" End If Home = Home + username + "" Set usr = userOU.Create("user", "CN=" & Surname & " " & FirstName) usr.Put "samAccountName", username & vbNullChar usr.Put "userPrincipalName", username & "@" & domainDNtoDNS(domainDN) & vbNullChar usr.Put "description", "Year " & YearLevel & " 2012" & vbNullChar usr.Put "homeDrive", "U:" & vbNullChar usr.Put "homeDirectory", "\\myServer\" & username & "$" & vbNullChar 'usr.put "userAccountControl", UF_NORMAL_ACCOUNT Or UF_PASSWD_CANT_CHANGE Or UF_DONT_EXPIRE_PASSWD usr.Put "displayName", Surname & " " & FirstName & vbNullChar usr.Put "sn", Surname & vbNullChar usr.Put "givenName", FirstName & vbNullChar usr.Put "telephoneNumber", "TelePhone Number" & vbNullChar usr.Put "postalCode", "Your Post Code" & vbNullChar usr.Put "st", "State Name" & vbNullChar usr.Put "l", "City Name" & vbNullChar usr.Put "c", "AU" & vbNullChar usr.Put "co", "Australia" & vbNullChar usr.Put "company", "Company Name" & vbNullChar usr.Put "streetAddress", "Your Stree Address" & vbNullChar usr.Put "scriptPath", "logon.bat" & vbNullChar usr.Put "homeDirectory", Home & vbNullChar usr.SetInfo Set secDescriptor = usr.Get("ntSecurityDescriptor") Set dACL = secDescriptor.DiscretionaryAcl usr.Put "ntSecurityDescriptor", Array(secDescriptor) usr.SetInfo usr.SetPassword Password usr.AccountDisabled = False usr.Put "pwdLastSet", 0 usr.SetInfo userDN = usr.Get("distinguishedName") On Error Resume Next MkDir Home 'MkDir Home & "\User Profile" group = "Students" 'netshare = Shell("net share " & username & "$=f:\students\grade" & YearLevel & "\" & username & " /GRANT:everyone,FULL") Set grp = GetObject("LDAP://CN=" & group & ",CN=Users," & domainDN) grp.Add "LDAP://" & userDN Select Case HomeGroup Case "LC" group2 = "LC_students,OU=LC,OU=Students" Case else group2 = "Year" & YearLevel & ",OU=Students" end select Set grp2 = GetObject("LDAP://CN=" & group2 &","& domainDN) grp2.Add "LDAP://" & userDN statement = "DACL " & Chr(34) & "Set" & Chr(34) & ", " & Chr(34) & "FILE://" & Home & "\" & Chr(34) & ", " & Chr(34) & username & ":C,Administrators:F" End Function

About Lei

I am an IT specialist with over 10 year experience - years on Automation, on-Premise or Azure.

I am happy to develop however never want be a full time developer. Only do what I have to do. If it has to be PowerShell,HTML, PHP, CSS, C#, VBS or JS, front end or backend, so be it, doesn't matter!

Spent years with Windows, SCCM, SharePoint, SQL and Exchange servers. For last several years, I have been actively working under On Premise > Azure environment.

THERE IS NO WAY BACK!!!

Current Certificates:
    Microsoft® Certified-
  • -Enterprise Administrator
  • -Database Administrator
  • -SharePoint Administrator
  • -Administering and Deploying SCCM 2012
Red Hat Certified Technician
ITIL V3 Foundation - Practitioner

Working on Azure Certificates now and hopefully they can stop upgrading their questions one day! GIVE ME A BREAK!

Contact Lei

Name *
Email *
Comments *

Traffic since 10/08/2016

Today13
Yesterday83
This week637
This month1933
Total416293

Visitor Info

  • IP: 54.221.73.186
  • Browser: Unknown
  • Browser Version:
  • Operating System: Unknown

Who Is Online

1
Online

2017-12-17

Login