Creating a batch of Active Directory accounts for SharePoint with the help of Excel

I recently deployed an extranet using SharePoint and Active Directory (AD). At my company, when a new extranet is requested, the request is typically accompanied with a list of new user accounts that should be created as well. Sometimes this list can contain over 20 accounts.

Using the Active Directory Users and Computers applet is not my favorite interface for creating accounts, and especially not for a large batch of them. I found myself having to edit an account 3 times to get it to appear the way I wanted in AD and in SharePoint. I looked into my scripting options, and ran across dsadd. This command has everything I need in order to create AD accounts in one step. However, it's very tedious to have to type the command given the customization I wanted and to remember the syntax rules.

Using Excel to create dsadd commands

To make this process easier, I created an Excel spreadsheet to generate the dsadd command based on a collection of cells. I've used Excel several times in the past to generate batches of commands or SQL statements. Even if you don't need a dsadd command, this spreadsheet is a useful reference for building other batches of commands.

After downloading the spreadsheet, you'll need to fill in the data for your company's Active Directory and the OU you'll be creating the accounts in. You'll then probably want to extend the cell formulas beyond the single row that's included. After you've filled in everything, copy the cells in the script column and paste it into a text editor like Notepad. Save it as a cmd file, and you can double click it to execute all the scripts.

You can add or remove more columns to accommodate the other dsadd parameters. Regarding additional information fields, I was only interested in company and department.

Tip: If you'd like to save the results of the commands, add an append redirection operator to the call to the script file from a console screen. This would be helpful to find out what went wrong if some of your commands failed.

For example,

createaccounts.cmd >> c:\dsadd.log 2>&1

will send all the commands and any errors to the dsadd.log file instead of the console. It's important to remember to add 2>&1 at the end, since dsadd sends errors to stderr, not stdout.

Adding a batch of user accounts to a SharePoint site collection

After the accounts have been added to AD, you can reuse the spreadsheet to add multiple accounts to SharePoint. Following are the steps I've used to translate a column of user account names into a semicolon-delimited list for the add user screen in SharePoint.

1. Copy the column of cells that have the account name (Domain\username) and Paste Special into Word to avoid it creating a table. You only want unformatted text.
2. Replace each line feed character (type ^p into the find box) with a semicolon.
3. Copy and paste into the SharePoint add user screen.

Technorati Tags: SharePoint,Excel,Active Directory