This sql script renames all duplicate emails with an incremented prefix (the user who signed up first keeps the original email, the others will be "1xyz@example.com") and does a few other things. I prefixed it so that the email will still be valid against a regex filter (I had problems with "xyz@example.com1" when requesting a password) as well as so that you can still tell what the user's old email was in case of a problem. I use ActiveSocial 2-stage verification (it is more advanced than the core's Authorization functionality as it allows the user to log on but does not add them to a role (let's called it "Verified") so you can let them do some stuff but restrict them from other things like posting on the forums. You can also target them with messages to verify their account - create a player profile on PokerDIY to see it in action.
The Verified role is very useful. For example, I want to use Axon to detect bouncing email addresses and remove these users from this role. This means when I send newsletters (or channel my module mails through the Axon mail sender) it can reset dodgy emails to unverified and when these users login again they'll have to resend their verification email (a bit like Twitter does when your mails bounce back to it). Note: I have requested the ability to remove users from a role on bounced email in Axon from OnyakTech - hopefully it is on the roadmap soon!
The script removes all changed emails from the Verified role (if you are not using AS 2-stage verification you can just leave this part commented out). Once you have renamed the email it obviously will never reach the user and so you don't want to be sending these emails any mails as you risk getting spam blacklisted for bounced mails.
It also inserts all changed users into a role (lets call it "EmailChangedRole") - this is so that you can target these users when they log on (you could put a module up saying "We had to change your email as it was a duplicate or something - please change it now"). It also is just a way of referencing who was changed in case you need it. You can of course leave it commented out to skip this step.
In both the roles above you will need to have created them through the UI and noted the RoleID for each one and change the script before running.
In the Select script to identify users there is a WHERE clause for emails that might require duplicates (on PokerDIY I have an email for all poker league guests that is the same - this user can never log on so it is not a problem - they must not be changed so are excluded - you can just leave this line uncommented).
Now you can run the script (all update, insert and delete statements are commented out so the script will only report back duplicates). When you are happy (and have backed up, bla bla) - then you can uncomment the lines you want. At the very least the Update statements to the Users and aspnet_Membership need to be uncommented for it to work.
Run the script and note if there are any errors. You can run the SELECT statement at the bottom again to make sure all duplicates are gone. Now you can open your Web.Config file and change the RequiresUniqueEmail to "true". Your Forgotten Password page will automatically show an email textbox now too (I tweaked all the language strings to be more helpful). I have not tested the core Sign Up page but the ActiveSocial sign up page now correctly traps for duplicate emails.
Test Forgotten Password with a username and email and make it sure you can log on with a user that had a duplicate email.
Remember that you will not be able to send those users any emails through your UI as their emails are now invalid, but you can at least target them or contact the original email if necessary.
Suggestions or improvements welcome!
/*
Renames duplicate emails by appending an increment on the end (so you can see what the email was). Also removes it from a specified role (optional) and adds it into a specified role (optional) for reference purposes.
You can then target these roles on your website (e.g. Your email is invalid - please reverify it. This works well with ActiveSocial 2-stage verification
--Tested only on DNN 5.2.2
--I have not tried this on multiple portals - TEST before using (the update/delete/inserts statements are commented out to start with - remove the comments to active it once you have filled in your portal detail variables and tested it (after backing up ;)
*/
DECLARE @PortalId int
DECLARE @VerifiedRoleID int
DECLARE @EmailChangedRoleID int
DECLARE @ApplicationID nvarchar(256)
--SET THESE TO YOUR PORTAL VARIABLES
SET @PortalId = 6
--role to remove the user from if their email is changed to invalid (if using AS 2-stage Verification
SET @VerifiedRoleID = 301
--role to insert all changed email users into so you can target them if they login or for reference purposes
SET @EmailChangedRoleID = 316
--this is the ApplicationID from the aspnet_Applications table that matches your portal. You need this especially if you have multiple portals on the same DB with shared usernames - this script is per portal only.
--You can do this query to work out which is your portal Application ID (or make a new user and do a query on the aspnet_Membership for that email and check the ApplicationID)
--SELECT * FROM aspnet_Applications
SET @ApplicationID = '3D3A4E41-D8EC-4466-AB75-10A493C81876'
DECLARE acursor CURSOR FOR
SELECT u.UserId, u.Email, u.DisplayName, u.LastName from dbo.Users as U INNER JOIN
(SELECT Email, Num FROM (
Select Email, Count(u.Email) as num from dbo.Users as u INNER JOIN dbo.UserPortals as up on up.UserId = u.UserId AND up.PortalId = @PortalId
Group By Email) as n WHERE Num > 1) as d on d.Email = u.Email
--exclude special users (if you have this requirement)
--and u.email not in ('xyz@example.com', '123@example.com')
ORDER BY u.Email, u.UserId
DECLARE @UserId int
DECLARE @Email nvarchar(100)
DECLARE @NewEmail nvarchar(100)
DECLARE @DisplayName nvarchar(200)
DECLARE @LastName nvarchar(200)
DECLARE @TotalCount int
DECLARE @tmpEmail nvarchar(200)
DECLARE @tmpCount int
SET @tmpEmail = ''
DECLARE @prefix nvarchar(10)
SET @TotalCount = 0
OPEN acursor
FETCH NEXT FROM acursor INTO @UserId, @Email, @DisplayName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @tmpEmail <> @Email
BEGIN
SET @tmpCount = 0
END
IF @tmpCount > 0
BEGIN
SET @prefix = CAST(@tmpCount as varchar(10))
IF EXISTS(SELECT UserId FROM dbo.Users WHERE Email = @Email + @prefix)
SET @prefix = CAST(@tmpCount as varchar(10))
--{x}OldEmail (so it still passes Regex checkers)
SET @NewEmail = @prefix + @Email
SET @TotalCount = @TotalCount + 1
--Remove comment hyphens in front of UPDATE to make changes
-- UPDATE dbo.Users SET Email = @NewEmail WHERE UserId = @UserId
--Remove comment hyphens in front of UPDATE to make changes
--Need to update it in the aspnet_membership table too
-- UPDATE dbo.aspnet_Membership
-- SET Email = @NewEmail, LoweredEmail = LOWER(@NewEmail)
-- WHERE aspnet_Membership.UserID =
-- (
-- SELECT AM.UserID
-- FROM aspnet_Membership AM
-- INNER JOIN aspnet_Users AU ON AU.UserID = AM.UserID
-- INNER JOIN Users U on U.UserName = AU.UserName
-- WHERE U.UserID = @UserID AND AU.ApplicationID = @ApplicationID
-- )
PRINT N'result ' + @Email + @prefix
--Remove comment hyphens in front of IF Statement if using ActiveSocial 2-Stage Verification
--Remove this user from the Verified Role if they are in it so they are prompted to reverify their email
--If not using AS Verified 2-stage registration then comment this out
-- IF EXISTS(SELECT UserId FROM UserRoles WHERE RoleID = @VerifiedRoleID AND UserID = @UserId)
-- BEGIN
-- DELETE FROM UserRoles WHERE RoleID = @VerifiedRoleID AND UserID = @UserId
-- PRINT N'deletedverifiedrole ' + @NewEmail
-- END
--Remove comment hyphens in front of IF Statement if you wish to log the changed users into a role
--insert this user into the EmailChanged Role if they are not in it for reference purposes or to target them if they can log in
-- IF NOT EXISTS(SELECT UserId FROM UserRoles WHERE RoleID = @EmailChangedRoleID AND UserID = @UserId)
-- BEGIN
-- INSERT INTO UserRoles ([UserID],[RoleID],[ExpiryDate],[IsTrialUsed],[EffectiveDate])
-- SELECT @UserId, @EmailChangedRoleID, NULL, NULL, NULL
-- PRINT N'insertedchangedrole ' + @NewEmail
-- END
END
SET @tmpEmail = @Email
SET @tmpCount = @tmpCount + 1
FETCH NEXT FROM acursor INTO @UserId, @Email, @DisplayName, @LastName
END
CLOSE acursor
DEALLOCATE acursor
PRINT N'Total records: ' + CAST(@TotalCount as varchar(10))
/*
--Check if all are gone - Returns list of duplicate emails (change PortalID in SQL)
SELECT u.UserId, u.Email, u.DisplayName, u.LastName from dbo.Users as U INNER JOIN
(SELECT Email, Num FROM (
Select Email, Count(u.Email) as num from dbo.Users as u INNER JOIN dbo.UserPortals as up on up.UserId = u.UserId AND up.PortalId = @PortalID
Group By Email) as n WHERE Num > 1) as d on d.Email = u.Email
--exclude special users
--and u.email not in ('xyz@example.com', '123@example.com')
Order By u.Email, u.UserId
*/