Tip: Remove orphaned (super)users

In DNN 5, if you delete a portal, its users will show up in Host:: SuperUser Accounts. However, no reason to worry, hose users are not granted superuser privileges nor do they have access to any other portal in your DotNetNuke installation; the cause for them to show up is the superuser module, which shows all user records, which have no associated record in userportals table, like superusers. Starting with soft-delete users, if removing a portal, user records are not touched and those leftovers show up as Superusers and prevent re-using the usernames.

To fix this situation, you may run the following script from inside Host :: SQL:

DELETE
 FROM {databaseOwner}{objectQualifier}users
 WHERE userID NOT IN (SELECT userID from {databaseOwner}{objectQualifier}UserPortals)
 AND IsSuperuser = 0
 
DELETE
 FROM aspnet_membership
 WHERE UserID NOT IN (SELECT A.UserID 
                      FROM aspnet_users A 
                      INNER JOIN {databaseOwner}{objectQualifier}Users U 
                      ON A.UserName = U.UserName)

DELETE
 FROM aspnet_users 
 WHERE UserName NOT IN (SELECT UserName
                        FROM {databaseOwner}{objectQualifier}Users)

(tested with DotNetNuke version 5.3.1)