host posted on October 09, 2010 12:35
After going through a lot of pain trying to enable the unique email I realized that I had to get rid of the duplicated email address from the past first. Here is what I did to get things done:
Preparation: before getting started I had several widows open:
the Admin "User Account" search window
the Host sql window
the database table window
Step 1: Find the duplicated emails and do the soft delete
Open the database table either from the server or using remote connection
There are several tables related to users:
UserProfile
UserRoles
users
aspnet_membership
aspnet_users
Just open one table to work with. I found "aspnet_membership" is the easiest one to work with since it sort the users by email address. Quickly scan the list and you can find the duplicated email addresses easily this way. Once you see a duplicated email, copy and paste it into the already opened member search window and do a search by email. You will then get a list of users that using the same email account, keep one and delete the rest.
Repeat this process until you find all of them.
Step 2: After the soft delete, you need the hard delete
The above soft delete does not really remove all the users with duplicated email account from the db. You need to do a hard delete. It's almost impossible to manually delete all of them since each single user account lives in multiple tables. There is a script from this site , run this script on your "Host" sql window. Note, each time it only delete 1 record, so you may just repeatedly run this script until you get rid of all of them from all the related tables. It is pretty easy to hit the run button even 100 times.
Step3: Enable the unique email
Go to web.config and find "requiresUniqueEmail="false", make the false into "true".
Note: unless it's a brand new site otherwise you need to ensure there is no duplicated email first before changing the web.config file.
[Read the rest of this article...]