Adding Users in Bulk

Administrators encounter this scenario all the time: You show up on Monday morning, and the human resources (HR) department has a dozen new user accounts for you to set up. This setup doesn't include just accounts, either, but the new users' home directories, dial-in permissions, and more. Instead of spending the morning checking on your Microsoft Exchange Server or catching up on the latest news on the Web, you're stuck entering new-user information from a spreadsheet.

Scripting to the rescue! This type of task is not only well suited to scripting-based automation, it's also a great way to demonstrate several different scripting capabilities. Let me show you a script that can automate the task of adding new users to your Windows NT 4.0 domain.

Getting Started

For this example, let's assume you're working with an NT domain called Corporate that has a PDC called NT4PDC. You must set up a basic infrastructure to let your script work. First, create a Microsoft Excel spreadsheet such as the one that Figure 1 shows.

The spreadsheet should include information for each user that you're adding to the domain. In this example, I've included columns for the user's ID, full name, description, and home directory Universal Naming Convention (UNC) path, a column for a comma-separated list of user groups to which the user should belong, and a column for indicating whether the user needs to have dial-in permissions. You can distribute your spreadsheet to HR department personnel and ask them to use it when they submit new-user requests. (Notice that the list of user groups doesn't include the Domain Users group because, by default, all users belong to Domain Users.)

Next, create a System ODBC Data Source Name (DSN) that points to the spreadsheet, as Figure 2 shows.

(I recommend that you create a System DSN rather than a User DSN. Otherwise, if another user logs on to the same machine to run the script, the script will fail.) The DSN points only to the spreadsheet's location, so you can save new spreadsheets to the same location and the DSN will find them. In this example, I've created a DSN called Excel that uses the Excel ODBC driver to point to my spreadsheet. If your computer doesn't have an Excel driver, you must install the latest version of Microsoft Data Access Components (MDAC), which you can download from http://www.microsoft.com/data.

Finally, make sure that Windows Script Host (WSH) and Active Directory Service Interfaces (ADSI) are available on the computer on which you'll run the script. (Both are present on Windows 2000 Professional, Win2K Server, Win2K Advanced Server, and Win2K Datacenter Server computers.) Note that you must install ADSI even if you're going to be working with an NT domain. If you want to set up these components on an NT workstation, you must install WSH from the Microsoft Windows NT 4.0 Option Pack and ADSI from the Windows Platform software development kit (SDK), which is available from http://www.microsoft.com/msdownload/platformsdk/sdkupdate.

Writing the Script

When all the preliminary pieces are in place, you can get the script ready. Listing 1 shows the complete bulk-users.vbs script. Because a lot is going on in this script, I'll describe each section.

Part 1 of bulk-users.vbs uses ActiveX Data Objects (ADO) to create a connection to the ODBC DSN called Excel. Because that DSN points to my Excel spreadsheet, I have a database connection to the spreadsheet. Next, I use the Connection object's Execute method to retrieve all the information on Sheet1 of the spreadsheet. This information resides in an ADO Recordset object.

Part 2 of bulk-users.vbs uses ADSI to obtain a reference to the PDC. To use this script, you must replace NT4PDC with the name of the PDC in your environment. Changing the PDC name is easy because I've defined it in a variable rather than in each statement that requires the PDC name. Because the PDC's SAM is the source for the domain's account list, this reference lets me work with the domain accounts.

Part 3 of the script uses the Scripting Runtime Library's FileSystemObject object to open a new text file called passwords.txt. I use this .txt file to store the passwords I create for the new user accounts. The CreateTextFile method returns a reference to a TextStream object, which I store in the oTS variable. This step essentially makes the oTS variable represent the open text file, which in turn makes writing information to the file later easy.

The Work Begins

Now, all the preliminary scripting work has been done, and Part 4 of bulk-users.vbs can begin to create user accounts. The first few lines of Part 4 simply declare the variables that the script uses. The script then sets up the base path to the server on which the user's home directory will be created. Note that this path connects to the C$ administrative share.

The main work of this script is accomplished within the Do...Loop statement, which handles the record set one line at a time. The first six lines of code within the loop retrieve the new user's information from the record set and store that information in variables. (This step isn't strictly necessary, but it makes the information easier to work with.) Notice that the field names in the Recordset object correspond to the column names in the Excel spreadsheet. Next, I create a pseudo-random password by combining the first two characters of the new user's ID, the minutes from the system clock, the Julian date (i.e., a number from 1 to 365 indicating the day of the year), and the seconds from the clock. You can also create a password based on the username, Social Security number, or other information.

The next few lines actually create the user account in the domain. The Create method requires two parameters: the type of object you're creating (e.g., a User object) and the identifier (e.g., the user's ID). The method returns a reference to the newly created User object, which I assign to the oUserAcct variable. Capturing this reference in a variable lets the script immediately set the properties of the account, including the password, full name, description, home directory, and RAS permissions. Note that the script includes error checking to specify whether a problem occurred when the script tried to create the user account. For example, if the Excel spreadsheet contains a username that already exists, the script will encounter an error when it tries to create the duplicate username. The script will then display a message telling you about the error.

After setting the account properties, the script sets RAS permissions. Note that the value 9 permits dialing in, while the value 1 denies it. You can look up these values in the ADSI documentation, which is available at http://msdn.microsoft.com/library/default.asp. (Navigate to Networking and Directory Services; Active Directory, ADSI and Directory Services; SDK Documentation; Directory Services; Active Directory Service Interfaces (ADSI).) Next, the SetInfo method saves all the account properties to the domain, then the script obtains a fresh reference to the user account. When the account is saved, the domain generates a SID and sets other internal information (e.g., the account's creation date, initial security attributes). Obtaining a new reference to the account gives the script access to that internal information, which is required for the next major step—adding the user to the proper groups. Part 4A of the script accomplishes this step. Before moving on to Part 4A, I use the Write method of the TextStream object to save the user's ID and password to the text file. Writing passwords to a file is a potential security breach, of course, which is why you might prefer to create nonrandom passwords that you don't need to write to a file. If you're certain that the password file won't be compromised, though, this method is a convenient way to create passwords for new users.

Because the Excel spreadsheet can contain a comma-separated list of groups to which the user should belong, I used the VBScript's Split function to turn that list into a string array. The Split function looks for commas in the sGroups string and creates an array called sGroupList, in which each element in the array is one group name. I then use a For...Next statement to go through each element in the array. The uBound function tells the script how many elements are in the sGroupList array so that the script executes the loop the proper number of times. Within the For...Next loop, I use ADSI again to obtain a reference to the group to which I want to add the user. I use the oGroup variable to store the reference, then I use the Add method to add the user. The ADsPath property is an internal piece of information that the domain provides when the account is saved. After adding the user, I release the reference to the group by setting the variable to the Nothing keyword. This step isn't strictly necessary, but it's good scripting practice and helps improve performance.

When the user is in all the correct groups, I use the FileSystemObject object again to create the user's home directory. This process takes place in Part 4B of bulk-users.vbs. This step creates a new folder by using the sHomePath variable and appending the user's ID for the final folder name. (Make sure that the C:users folder already exists; otherwise, this operation will fail.)

Part 5 of the script releases the reference to the User object in preparation for the next user in the record set. The last line in the Do...Loop construct moves the record-set pointer to the next row so that the loop can work on the next user. If the record-set pointer moves beyond the last user, the End of File (EOF) property is set to True and the loop terminates.

Part 6 of the script runs after the Do...Loop construct has processed the last user. This part simply closes the Excel spreadsheet, closes the text file that contains the new passwords, and displays a dialog box that states the script has completed successfully. Figure 3 shows User Manager for Domains with the new user accounts in place.

Do It with a Script

Scripting is a great administrative tool because it lets you glue together various pieces of OS functionality to achieve terrific results. I used ADO, the Scripting Runtime Library, and ADSI—three relatively unrelated sets of technology—to perform a common, time-consuming administrative task. The examples here give you a good idea about how you can use a script to make complex tasks much easier and how to start exploring ADO and ADSI to come up with custom timesaving solutions.