Scrubbing a DotNetNuke Database for user info and passwords

If you’ve ever needed to send a backup of your DotNetNuke database to a developer for testing, you likely trust the developer enough to do so without scrubbing your data, but just to be safe it is probably best that you do take the time to scrub.

Before you do anything with the SQL below, make sure you have a backup of your website! I would recommend you do the following.

1. Backup your existing production database
2. Restore a backup of your production database as a NEW database
3. Run the scripts below on the NEW database
4. Shrink the NEW database
5. Backup the NEW database

Below I’ve included two SQL scripts that will help you with that data scrubbing, at least in respect to User emails and passwords.

This script updates the aspnet_membership table to set the password for ALL USERS to “dnnpassword” and the format and salt values. It also sets email addresses to a generic email.

   1:  update aspnet_Membership 

   2:   

   3:  set 

   4:  Password ='dnnpassword'

   5:  , PasswordFormat='0'

   6:  , PasswordSalt = ''

   7:  ,Email = 'sample@mysite.com'

   8:  ,loweredEmail = 'sample@mysite.com'

This script updates the DotNetNuke users table to remove the email address from your users (assuming they aren’t using their email address as their username).

   1:  update users 

   2:  set 

   3:  Email = 'sample@solo2.org'

Another problem you may run into is the size of your database, if you have a lot of content you might want to scrub the Search tables and then shrink the database. Check out my old blog post for the SearchItem t-sql. Depending on the size of your database the search clearing might take a while, it will also likely INCREASE the size of your database due to the number of transactions.

Shrink your database after running the scripts. Backup the shrunken database.

I’m sure there are probably other tables you can and should scrub, I’ll add those to this post as I come up with them!