I regularly import the Drupal database from our hourly backups into our local development server for testing purposes. That’s all well and good, but I really wanted a way to remove any genuine registered user data from the database so that I keep things nice and anonymous.
Drupal stores the user’s ID in the user_roles table if they have an extended role – i.e. anything above a simple registered user status. All of our team have extended roles, so it’s just a case of finding the users that don’t appear in the users_roles table. To do that you just need to execute the following script on the database you want to purge NOT YOUR PRODUCTION DATABSE.
DELETE FROM users WHERE users.uid NOT IN ( SELECT users_roles.uid FROM users_roles );
REMEMBER: this query is going to delete users, only use with a copy of your database, never on a production server.
You can also use this to select any profile data or user generated content as well (I just overwrite with filler text). Which is nice.