How to Bulk Create AD Users during a CRM Redeployment

When setting up a CRM test environment in a separate domain from where the production environment exists I find it preferable to replicate the production AD users in that test domain so that you can auto-map each CRM user.  Here’s an approach you can follow to bulk create each CRM user in your test environment’s Active Directory…


Firstly, we need to get a list of the CRM users, and we need that list formatted a certain way.  Here’s an SQL script you can use:

SELECT 'objectClass','DN','displayName','givenName','sn','samAccountName'

UNION ALL

SELECT 
	'objectClass' = 'user',
	'DN' = '"cn=' + [FullName] + ',OU=GTUsers,DC=GTDOMAIN,DC=COM"',
	'displayName' = [FullName],
	'givenName' = [FirstName],
	'sn' = [LastName],
	'samAccountName' = replace([DomainName],'SITCRM\','')
  FROM [SITCRM_MSCRM].[dbo].[SystemUserBase]
where [FirstName]  ''
and replace([DomainName],'SITCRM\','')  'administrator'

You will need to localise this script to your environments: 

  • On line 7 you need to specify an OU in your AD where you want your users created.   My domain is gtdomain.com and I created an OU there called GTusers.  Adjust these parameters as required.
  • On lines 11 and 14 I am striping out the domain name ‘SITCRM’ using a replace function.  Adjust this to the domain name of your source system.

Run the script in SQL Management Studio and confirm you get a header row followed by a row per CRM user that looks something like this:

image

Here’s a quick explanation of the columns:

  • objectclass: this should say ‘user’ – this our way of requesting to have users created in AD
  • DN: this specifies where in the AD our records should be created
  • displayName: this should be the full name of the user
  • givenName: first name of the user
  • sn: surname of the user
  • samAccountNAme: logon for the user

Ok, now we need this result set pushed out to a CSV file.  I do this by going into the Query Options

image

… and setting the Output format to Comma delimited and unchecking the option to include column headers in the result set:

image

Then I switch the Results To setting to File:

image

Finally, I execute the script again.  Because I asked the results to go to file I am prompted to specify a file location, so I save the file to my desktop under the name “users.csv”.  Run that:

image

Once generated we need to just a quick correction to the file.  If it got created with a .rpt extension go ahead and remove that, we want the file to have a .csv extension.  Then open the file in Notepad scroll to the bottom and delete the last couple of lines containing the record count at the end:

image

Ok, now to import this file into the AD.  Make sure you are logged in as a user that can administer the AD.  I’m not sure where you can run this from, I ran mine from the Domain Controller.   I copied my file to the C drive and then ran the following script:

csvde –i –f c:\users.csv

If you have success you will see a message like this:

image

And over in the AD you will have your users:

image

Hope this helps someone Smile

Advertisements

2 thoughts on “How to Bulk Create AD Users during a CRM Redeployment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s