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. However, to be safe, it’s best to take the time to scrub sensitive information.

Backup Process

Before executing any SQL scripts, ensure you have a full backup of your website. Follow these recommended steps:

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.

Data Scrubbing Scripts

Below are two SQL scripts to help anonymize user data, specifically for email addresses and passwords.

Resetting User Passwords and Emails

This script updates the aspnet_Membership table to set the password for all users to “dnnpassword” and replaces email addresses with a generic email.

UPDATE aspnet_Membership
SET
    Password = 'dnnpassword',
    PasswordFormat = '0',
    PasswordSalt = '',
    Email = '[email protected]',
    LoweredEmail = '[email protected]';

Removing User Emails in DotNetNuke Users Table

This script updates the Users table to replace email addresses with a generic one, assuming they are not using their email address as their username.

UPDATE Users
SET Email = '[email protected]';

Reducing Database Size

Another issue you may encounter is database size. If you have a lot of content, you may want to scrub the search tables and then shrink the database. Check out my old blog post for the SearchItem T-SQL.

Note: Clearing search tables can temporarily increase database size due to transaction logs.

Final Steps

1. Run the scripts above on your test database.
2. Shrink the database.
3. Backup the cleaned database.

There may be additional tables requiring scrubbing, and I’ll update this post as I identify them!