Getting registered users from your Drupal database

Posted on July 1, 2010 at 12:00 pm

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.

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.

Tags: , ,