What is the best way to remove a large number of users from a DNN website?

Thanks

Tom

Tom,

after making a backup of your database you could do the following: In Host :: SQL (or Settings :: SQL Console when you are using DNN 9.x) run the following script:

UPDATE {databaseOwner}{objectQualifier}Users SET IsDeleted = 1 WHERE IsSuperUSer = 0 AND Username NOT IN ('Admin', 'xyz')
GO

UPDATE {databaseOwner}{objectQualifier}UserPortals SET IsDeleted = 1 WHERE UserId IN (SELECT UserId FROM {databaseOwner}{objectQualifier}Users WHERE IsDeleted = 1)
GO

Please note: You do not want to delete the superusers (host users) and maybe some other users, please change the list at the end of the SQL statement accordingly, or remove the part in blue letters if not needed.

Then restart the application pool under Tools :: Restart Application (or Settings :: Server :: [Restart Application] button in DNN 9.x).

The users are now "soft-deleted", if you want to ultimately remove them you can do this under Admin :: User Accounts by clicking the "Remove Deleted Users" button. I have not found any way to do this in DNN 9.x, there seems to be no interface. So this SQL script might be helpful:

DELETE FROM {databaseOwner}{objectQualifier}Users WHERE IsDeleted = 1
GO

DELETE FROM aspnet_membership WHERE UserId NOT IN (SELECT UserId FROM aspnet_users WHERE Username IN (SELECT Username FROM {databaseOwner}{objectQualifier}Users))
GO

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

DELETE FROM {databaseOwner}{objectQualifier}CoreMessaging_UserPreferences WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserAuthentication WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserPortals WHERE IsDeleted = 1
GO

DELETE FROM {databaseOwner}{objectQualifier}UserProfile WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRelationshipPreferences WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRelationships WHERE RelatedUserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRelationships WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UserRoles WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

DELETE FROM {databaseOwner}{objectQualifier}UsersOnline WHERE UserID NOT IN (SELECT UserID FROM {databaseOwner}{objectQualifier}Users)
GO

Please note: Depending on the modules you installed there might be other tables that contain a reference to the UserId, e.g. threads and posts in a forum module, orders in a shop module and so on. This statement is only an example with no claim on completeness!

If anything went wrong with this you can restore your database backup, but don't blame me - it is all on your own risk :-)

Happy DNNing!
Michael