Delete DNN users programmatically

By Kateryna Sytnyk

December 12, 2008

DotNetNuke user inteface doesn't allow to delete authenticated users alltogether. 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)

 

Average (3 Ratings):
 
Want to help out?
 
 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share