How to delete users without any role in Drupal 7

We faced of one of the projects that we supported with large amount of spam registrations. But there were valid users with assigned roles that should not be deleted.

Short script was written to resolve this task:

require_once './includes/';
define('DRUPAL_ROOT', '<path_to_you_Drupal_folder>'); // optional
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL); // load Drupal to use Drupal API
$query = 'SELECT users.uid as uid FROM {users} LEFT JOIN {users_roles} ON users.uid = users_roles.uid WHERE users.uid != 0 AND users_roles.uid IS null ORDER BY users.uid DESC LIMIT 300';
$result = db_query($query);
while (
$row = $result->fetchObject()) {
'300 spam users were deleted!'

Let me explain this code in details:

If we would like to use Drupal API and DB connection in any separate script not in our custom Drupal module we need to start with these lines:

require_once './includes/';
define('DRUPAL_ROOT', '<path_to_you_Drupal_folder>'); // optional
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL); // load Drupal to use Drupal API

Attention! You should put your path instead ''.
Some times script can work without 'DRUPAL_ROOT' definition, but some times can't like in our case.

Authenticated User role ID is 2. But there is no any record with that role ID in {users_roles} table, so we should select all user IDs that have no records in {users_roles} table users_roles.uid IS null.

And do not forget to exclude Anonymous user record users.uid != 0.
Other wise we can delete record with 0 UID from {users} table, that serve for Anonymous user purpose.

Start to delete most resent users ORDER BY users.uid DESC.

Due to user_delete() function require pretty large number of SQL requests it is reasonable to set some limit LIMIT 300.

If there are a lot of spam users you can set cron task, for example:

*/02 * * * * /usr/bin/wget -O - -q -t 1 >/dev/null 2>&1


This is not Drupal way.
One of the approaches is to create Drush command and run it with Cron.

Did you mean 'sql-query' Drush command.

Unfortunately Drush is not accessible tool on some hostings :-(

The second way is to write hook_cron() implementation in your custom module.

yes, and put the same code in hook_cron

I can see quite a few issues with this, but the least you could do is make sure this script is not accessible to the entire internet. That's the least

this is one time solution, so you should put script in Drupal folder, run it, and delete it after work will be done

What if someone has access to your scripts like if google bot crawls you script? Optimum solution would be using hook_cron. Also it would be better to use EntiyFieldQuery and user_delete_multiple.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.
Enter the characters shown in the image.