DotNetNuke user inteface doesn't allow to delete authenticated users altogether. In some cases (for instance, re-importing users back and forth) it is necessary to have a way of removing all the users, as usually it's too many of them to do it manually.
Here is the code that will remove all the entries related to a User instance in DNN. It is identical to removing a user via DotNetNuke graphic interface. When clearing all the users, you probably want to keep host user, along with any other test users you want to preserve - specify those users' ids in the query below.
Remove All the Users Except of Some Specified
declare @rUser table (UserId int)
insert into @rUser (UserId) values (2)
insert into @rUser (UserId) values (1)
-- clear login info
DELETE from aspnet_Membership where UserId in
( SELECT au.UserId from aspnet_Users au join users u on au.username = u.username where u.userid not in (select * from @rUser) )
DELETE from aspnet_Users where UserId in
( SELECT au.UserId from aspnet_Users au join users u on au.username = u.username where u.userid not in (select * from @rUser) )
-- clear profile properties
DELETE FROM UserProfile where UserID in
( SELECT UserId from users where userid not in (select * from @rUser) )
-- clear portal relationships with users
DELETE FROM UserPortals where UserID in
( SELECT UserId from users where userid not in (select * from @rUser) )
-- clear role relationships with users
DELETE FROM UserRoles where UserID in
( SELECT UserId from users where userid not in (select * from @rUser) )
------------------------------
-- CLEAR OUT CLIENT SPECIFIC INFO
------------------------------
------------------------------
-- END CLEAR OUT CLIENT SPECIFIC INFO
------------------------------
-- finally remove core user profile info
DELETE FROM users where userid not in (select * from @rUser)
--no need for these
/*
aspnet_Profile
aspnet_UsersInRoles
UserAuthentication
*/
rollback transaction CLEANDNN
Remove Only Some Users
declare @rUser table (UserId int)
insert into @rUser (UserId) values (9)
-- clear login info
DELETE from aspnet_Membership where UserId in
( SELECT au.UserId from aspnet_Users au join users u on au.username = u.username where u.userid in (select * from @rUser) )
DELETE from aspnet_Users where UserId in
( SELECT au.UserId from aspnet_Users au join users u on au.username = u.username where u.userid in (select * from @rUser) )
-- clear profile properties
DELETE FROM UserProfile where UserID in
( SELECT UserId from users where userid in (select * from @rUser) )
-- clear portal relationships with users
DELETE FROM UserPortals where UserID in
( SELECT UserId from users where userid in (select * from @rUser) )
-- clear role relationships with users
DELETE FROM UserRoles where UserID in
( SELECT UserId from users where userid in (select * from @rUser) )
------------------------------
-- CLEAR OUT CLIENT SPECIFIC INFO
------------------------------
------------------------------
-- END CLEAR OUT CLIENT SPECIFIC INFO
------------------------------
-- finally remove core user profile info
DELETE FROM users where userid in (select * from @rUser)