Adding Users from Excel
Adding users to your domain in bulk can be a great timesaver, especially in rapidly growing organizations. This example lists users in an Excel spreadsheet, and actually uses ActiveX Data Objects (ADO) to read information from the spreadsheet.
You need to do a bit of preparation to use this script. First, create a new Excel spreadsheet. The spreadsheet should look something like Table 31.1.
Notice that the Groups column is a comma-delimited list of group names, with no spaces after the commas.
Table 31.1. Sample Excel Spreadsheet for Adding Users|
DonJ | Don Jones | Administrator | Donj | Domain Admins | Y | GregM | Greg Marino | Sales | Gregm | Sales,Execs | N |
You also need to create an ODBC DSN (Data Source Name) that points to the spreadsheet. On Windows XP, open the Administrative Tools program group, and select the Data Sources item. Create a new System DSN that uses the Microsoft Excel driver. Name the DSN "Excel." When you make the Excel spreadsheet, be sure to create your columns and rows on Sheet1, and don't change the sheet name.
 Adding Users
Listing 31.6 shows the complete script, which uses the Excel sheet and DSN you set up earlier.
Listing 31.6. AddUsers.vbs. Adds users in bulk from an Excel spreadsheet.
' PART 1: Open up the Excel spreadsheet
' using ActiveX Data Objects
Dim oCN
Set oCN = CreateObject("ADODB.Connection")
oCN.Open "Excel"
Dim oRS
Set oRS = oCN.Execute("SELECT * FROM [Sheet1$]")
' PART 2: Get a reference to the
' Windows NT domain using ADSI
Dim oDomain
Set oDomain = GetObject("WinNT://NT4PDC")
' PART 3: Open an output text file
' to store users' initial passwords
Dim oFSO, oTS
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.CreateTextFile("c:\passwords.txt",True)
' PART 4: For each record in the recordset,
' add the user, set the correct user
' properties, and add the user to the
' appropriate groups
' create the necessary variables
Dim sUserID, sFullName, sDescription
Dim sHomeDir, sGroups, sDialIn
Dim sPassword, oUserAcct, oFolder
Dim sGroupList, iTemp, oGroup
' define the base path for the home
' directories to be created in
Dim sHomePath
sHomePath = "\\iridis1\c$\users\"
' now go through the recordset one
' row at a time
Do Until oRS.EOF
' get the user information from this row
sUserID = oRS("UserID")
sFullName = oRS("FullName")
sDescription = oRS("Description")
sHomeDir = oRS("HomeDirectory")
sGroups = oRS("Groups")
sDialIn = oRS("DialIn")
' make up a new password
sPassword = Left(sUserID,2) & DatePart("n",Time) & _
DatePart("y",Date) & DatePart("s",Time)
' create the user account
Set oUserAcct = oDomain.Create("user",sUserID)
' set account properties
oUserAcct.SetPassword sPassword
oUserAcct.FullName = sFullName
oUserAcct.Description = sDescription
oUserAcct.HomeDirectory = sHomeDir
' set RAS permission
If sDialIn = "Y" Then
oUserAcct.RasPermissions = 9
Else
oUserAcct.RasPermissions = 1
End If
' save the account
oUserAcct.SetInfo
' get a reference to the new account
' this gets us a valid SID & other info
Set oUserAcct = GetObject("WinNT://NT4PDC/" & sUserID & _
",user")
' write password to file
oTS.Write sUserID & "," & sPassword & vbCrLf
' PART 4A: Add user account to groups
' use the Split function to turn the
' comma-separated list into an array
sGroupList = Split(sGroups, ",")
' go through the array and add the user
' to each group
For iTemp = 0 To uBound(sGroupList)
' get the group
Set oGroup = GetObject("WinNT://NT4PDC/" & _
sGroupList(iTemp) & ",group")
' add the user account
oGroup.Add oUserAcct.ADsPath
' release the group
Set oGroup = Nothing
Next
' PART 4B: Create the user's Home Directory
' (append UserID to the Home Path variable)
Set oFolder = oFSO.CreateFolder(sHomePath & sUserID)
' PART 5: All done!
' release the user account
Set oUserAcct = Nothing
' move to the next row in the recordset
oRS.MoveNext
Loop
' PART 6: Final clean up, close down
oRS.Close
oTS.Close
WScript.Echo "Passwords have been written to c:\passwords.txt."
You need to adjust the server names and domain to suit your environment, but otherwise this script should run unaltered in either an NT or Active Directory domain.
Adding Users-Explained
The script starts by creating an ADO connection object and opening the "Excel" DSN. Because the DSN is tied to your spreadsheet, the connection object now represents that spreadsheet.
' PART 1: Open up the Excel spreadsheet
' using ActiveX Data Objects
Dim oCN
Set oCN = CreateObject("ADODB.Connection")
oCN.Open "Excel"
Next, the script queries all rows from Sheet1 into a Recordset object. The recordset treats the first row as a column header, which sets up the column names.
Dim oRS
Set oRS = oCN.Execute("SELECT * FROM [Sheet1$]")
Now, all of the users you want to create are in the Recordset. Each record in the Recordset represents a single user. The next preparatory step is to connect to a domain or domain controller by using ADSI.
' PART 2: Get a reference to the
' Windows NT domain using ADSI
Dim oDomain
Set oDomain = GetObject("WinNT://NT4PDC")
oDomain now represents the domain, or a domain controller. Finally, the script opens up an output text file. Because we'll be creating new users, they need passwords; the script generates unique passwords for each user and saves them to this file.
' PART 3: Open an output text file
' to store users' initial passwords
Dim oFSO, oTS
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.CreateTextFile("c:\passwords.txt",True)
The script needs to define a number of variables that will hold information about the user being created.
' PART 4: For each record in the recordset,
' add the user, set the correct user
' properties, and add the user to the
' appropriate groups
' create the necessary variables
Dim sUserID, sFullName, sDescription
Dim sHomeDir, sGroups, sDialIn
Dim sPassword, oUserAcct, oFolder
Dim sGroupList, iTemp, oGroup
This is where you define the base path for your users' home directories. Typically, this is a shared folder. Note that one thing this script does not do is apply NTFS permissions; that's an enhancement you can make using WMI, if you like.
' define the base path for the home
' directories to be created in
Dim sHomePath
sHomePath = "\\iridis1\c$\users\"
The script now uses a Do…Loop construct to go through each record in the Recordset. The loop stops executing when the last row is passed, setting the Recordset object's EOF property to True.
' now go through the recordset one
' row at a time
Do Until oRS.EOF
Within the loop, the script first pulls information from the Recordset into variables, making the information a bit easier to access and work with. Notice that the Recordset object allows you to refer to each column of information by name, based on the names defined in the header row of the spreadsheet.
' get the user information from this row
sUserID = oRS("UserID")
sFullName = oRS("FullName")
sDescription = oRS("Description")
sHomeDir = oRS("HomeDirectory")
sGroups = oRS("Groups")
sDialIn = oRS("DialIn")
The new user needs a password, so the script makes one up. This takes the first two characters of the user's ID, the current minutes from the system clock, the current Julian date, and the current seconds. Not a fantastic password, but should be unique for each user.
' make up a new password
sPassword = Left(sUserID,2) & DatePart("n",Time) & _
DatePart("y",Date) & DatePart("s",Time)
Now, the script can create the user account. This uses the domain object's Create method to create a new object of the "user" class, having the user ID specified as the new object's name.
' create the user account
Set oUserAcct = oDomain.Create("user",sUserID)
Several properties of the new account can be set up right away, including its password, full name, home directory, and description.
' set account properties
oUserAcct.SetPassword sPassword
oUserAcct.FullName = sFullName
oUserAcct.Description = sDescription
oUserAcct.HomeDirectory = sHomeDir
The dial-in permissions flag can also be set.
' set RAS permission
If sDialIn = "Y" Then
oUserAcct.RasPermissions = 9
Else
oUserAcct.RasPermissions = 1
End If
Finally, the new object's SetInfo method saves the new object and its properties. This is necessary before the domain will assign a unique security identifier (SID) to the account. Remember that, in a domain, groups contain the SIDs for the member accounts, not the member accounts' names. To update group membership for this user, then, the script has to save the user object and then retrieve its new SID. That SID can be added to the group membership lists.
' save the account
oUserAcct.SetInfo
Requerying ADSI for the user that was just created makes the SID available when we need it.
' get a reference to the new account
' this gets us a valid SID & other info
Set oUserAcct = GetObject("WinNT://NT4PDC/" & sUserID & _
",user")
Writing the password to a file allows someone to communicate it to the new user.
' write password to file
oTS.Write sUserID & "," & sPassword & vbCrLf
Now, it's time to add the user to groups. Because there can be more than one group, the Split() function is used to create an array of group names.
For more on the Split() function, refer to Chapter 9, "Working with Arrays."
' PART 4A: Add user account to groups
' use the Split function to turn the
' comma-separated list into an array
sGroupList = Split(sGroups, ",")
Now the script can examine each element of the array one at a time. Each element contains a single group name.
' go through the array and add the user
' to each group
For iTemp = 0 To uBound(sGroupList)
The script first queries ADSI to get a reference to the specified group.
' get the group
Set oGroup = GetObject("WinNT://NT4PDC/" & _
sGroupList(iTemp) & ",group")
Then, the script uses the group's Add method to add the user's ADsPath property, which is the user's SID.
' add the user account
oGroup.Add oUserAcct.ADsPath
Finally, the group object is released so that the next group in the list can be retrieved.
' release the group
Set oGroup = Nothing
Next
With the groups out of the way, the script can create the user's home directory folder. This is created under the base path specified earlier.
' PART 4B: Create the user's Home Directory
' (append UserID to the Home Path variable)
Set oFolder = oFSO.CreateFolder(sHomePath & sUserID)
That's all the user object is needed for. It can be released, and the script can move on to the next user.
' PART 5: All done!
' release the user account
Set oUserAcct = Nothing
' move to the next row in the recordset
oRS.MoveNext
Loop
When all of the users are processed, the script closes the Recordset object, closes the output text file, and reminds you where you can find the text file containing the new users' passwords.
' PART 6: Final clean up, close down
oRS.Close
oTS.Close
WScript.Echo "Passwords have been written to c:\passwords.txt."
It's a powerful script that not only demonstrates how to effectively use ADSI, but also how to use ADO for simple data-retrieval operations. Believe it or not, you could easily modify this script to use Access databases instead of Excel. Simply change the "Excel" DSN to point to an Access database, and change one line of code.
Dim oRS
Set oRS = oCN.Execute("SELECT * FROM [Sheet1$]")
Change "[Sheet1$]" to the name of the Access database table containing your users. As long as the table's column names match the ones I used in the Excel spreadsheet, this script will work just fine.
| 
