Will "the Mighty" Strohl

Delete Soft Deleted Users After Deleting Portals

I ran into an interesting situation last night.  I don’t know why I hadn’t noticed this in the past.  My blog site was originally one of many in a single DotNetNuke® installation or instance.  Since it has grown in popularity and traffic, I had to move it to it’s own standalone DNN instance.  The quickest way to make that happen was to move a copy of the original instance, and delete all of the other portals that came with it.  In doing so, this left all of the users from the other portals still in the Users table in the database.

WARNING!!! Don’t just run the queries here. Read the post in detail first. Also, BACKUP your database before manually changing any data.  You may severely mess up your site. You’ve been warned!

At first, I kind of panicked, because I was looking at the users in my Superusers Account module.  It was 100+ pages of users!  We all know that there is probably not a single site out there that has 10 super users, much less 100+ pages worth of super users.  In the first page, there were several test accounts, so my first thought was that I had been hacked at some point.  However, I didn’t take the history of the portal into account. 

As it turns out, I had learned in a very scary way how DNN handles user accounts when portals are deleted.  I am not sure of the thought process behind it, but the users are soft deleted from the portals that get deleted.  In this situation, this has a couple of distinctive attributes to take note of.

  • The IsDeleted column in the Users table is 1
  • The PortalId column in the UserPortals table is NULL

You can see these attributes by running the following query:

   1: SELECT up.[PortalId], u.[UserID], u.[Username], u.[FirstName], u.[LastName], u.[IsSuperUser], u.[Email], u.[DisplayName], u.[IsDeleted] 
   2: FROM [databaseOwner}[{objectQualifier}Users] u 
   3: LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId] 
   4: ORDER BY up.[PortalId], up.[UserId];
   5: -- OR --
   6: SELECT up.[PortalId], u.[UserID], u.[Username], u.[FirstName], u.[LastName], u.[IsSuperUser], u.[Email], u.[DisplayName], u.[IsDeleted] 
   7: FROM [dbo].[Users] u 
   8: LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId] 
   9: ORDER BY up.[PortalId], up.[UserId];

At first glance, this appears to be an easy fix.  That is, if you’re not paying attention.  Just delete all of the users that are no longer associated with a portal.  Let’s try it!

   1: DELETE FROM {databaseOwner}[{objectQualifier}Users] 
   2: WHERE [UserId] IN (
   3:     SELECT u.[UserId] 
   4:     FROM {databaseOwner}[{objectQualifier}Users] u 
   5:     LEFT OUTER JOIN {databaseOwner}[{objectQualifier}UserPortals] up ON u.[UserId] = up.[UserId] 
   6:     WHERE up.[PortalId] IS NULL
   7: )
   8: -- OR -- 
   9: DELETE FROM [dbo].[Users] 
  10: WHERE [UserId] IN (
  11:     SELECT u.[UserId] 
  12:     FROM [dbo].[Users] u 
  13:     LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId] 
  14:     WHERE up.[PortalId] IS NULL
  15: )

Well, that didn’t work so well.  What I failed to notice is what you might not have as well.  Super User accounts are not associated to a portal.  So the previous query would have deleted all of those users as well.  D’oh!  Luckily, I backup my database before I do anything like this, so I just restored it real quick. 

To delete all of the soft deleted users from the other portals, we need to adjust the previous query to filter out the super user accounts.

   1: DELETE FROM {databaseOwner}[{objectQualifier}Users] 
   2: WHERE [UserId] IN (
   3:     SELECT u.[UserId] 
   4:     FROM {databaseOwner}[{objectQualifier}Users] u 
   5:     LEFT OUTER JOIN {databaseOwner}[{objectQualifier}UserPortals] up ON u.[UserId] = up.[UserId] 
   6:     WHERE up.[PortalId] IS NULL AND NOT u.[IsSuperUser] = 1
   7: )
   8: -- OR -- 
   9: DELETE FROM [dbo].[Users] 
  10: WHERE [UserId] IN (
  11:     SELECT u.[UserId] 
  12:     FROM [dbo].[Users] u 
  13:     LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId] 
  14:     WHERE up.[PortalId] IS NULL AND NOT u.[IsSuperUser] = 1
  15: )

Viola!  That did the trick.  All of the goodness, none of the oversights.  I hope this helps you too!



blog comments powered by Disqus