One of the changes to DNN over the past year or so was the way that users were handled when they were deleted. Originally when a user was deleted from DotNetNuke they were hard deleted and completely removed from the database. This would cause problems if you had multiple portals (websites) in your instance of DNN and had users who were shared across those portals.
To correct for the issue, users are now soft deleted from a portal, there is a UserPortals table that contains an isdeleted flag whenever someone is removed.
This is fine and dandy in most cases, but what if you really want to delete the user completely from the database? Well then you need to come up with some SQL to do so as there are a number of tables that need to get hit. While perusing through the forums today I came across a post in which a user was having problems with deleted users and the requirement for unique email addresses in the web.config. If the some of the users with unique email addresses were soft deleted the user who wasn’t deleted couldn’t login to the site. To correct for this I wrote some SQL to attempt to hard delete the users.
A word of Warning: I’ve done only a limited amount of testing on this, you should definitely backup your database before running ANY custom SQL. Also be sure to test this out in a test environment first to make sure you don’t have any negative impacts. This doesn’t handle custom modules and their data with user references, so you might have to delete a few other items before you can delete the data referenced below.
delete {databaseOwner}{objectQualifier}userroles where userid=(select top 1 userid from {databaseOwner}{objectQualifier}userportals where isdeleted=1 and userid not in (select userid from {databaseOwner}{objectQualifier}userportals where isdeleted=0))
delete {databaseOwner}{objectQualifier}userprofile where userid=(select top 1 userid from {databaseOwner}{objectQualifier}userportals where isdeleted=1 and userid not in (select userid from {databaseOwner}{objectQualifier}userportals where isdeleted=0))
delete aspnet_membership where userid=( select userid from aspnet_users where username=(select username from {databaseOwner}{objectQualifier}users where userid=(select top 1 userid from {databaseOwner}{objectQualifier}userportals where isdeleted=1 and userid not in (select userid from {databaseOwner}{objectQualifier}userportals where isdeleted=0))))
delete aspnet_users where username=(select username from {databaseOwner}{objectQualifier}users where userid=(select top 1 userid from {databaseOwner}{objectQualifier}userportals where isdeleted=1 and userid not in (select userid from {databaseOwner}{objectQualifier}userportals where isdeleted=0)))
delete {databaseOwner}{objectQualifier}userportals where userid=(select top 1 userid from {databaseOwner}{objectQualifier}userportals where isdeleted=1 and userid not in (select userid from {databaseOwner}{objectQualifier}userportals where isdeleted=0))
delete {databaseOwner}{objectQualifier}users where userid not in (select userid from {databaseOwner}{objectQualifier}userportals) and issuperuser=0