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:
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…
… and setting the Output format to Comma delimited and unchecking the option to include column headers in the result set:
Then I switch the Results To setting to File:
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:
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:
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:
And over in the AD you will have your users:
Hope this helps someone