Question 1: I would like an sql statement to use in phpMyAdmin to delete the users with the role: authenticated user. Looks like rid = 2.

Related Question 2: I show thousands of users in admin/people but the query: SELECT * FROM drup_users WHERE 1
only shows 30 users. Shouldn't they all be there?

Related Question 3: I created several roles but only the three standard roles appear using: SELECT * FROM drup_role WHERE 1 Where are all the roles I created?

I think I'm having some gross conceptual error. I would appreciate any guidance :) Thanks!

  • 1
    We don't have an SQL writing service here, you'll need to write that yourself (it would be fairly complicated, you'll need to delete field data and take into account data for contrib modules etc if you bypass the API)...regarding the other 2 questions - are you sure you're looking in the right database/tables? Drupal stores users and roles in the DB and I don't think that's pluggable, so it's likely they're in a table somewhere
    – Clive
    Jan 29, 2015 at 0:58
  • Thanks Clive :) They are just spam bot accounts. There are no nodes associated with them. Looks like the correct database. There is only one with my hosting account. And that db shows my admin account data correctly. As well as the nodes I have added. It has me stumped for sure. Jan 29, 2015 at 1:31
  • +1 to @Clive here. You really should not bypass the API. Messing with the database directly could mean bad news. Look at user_delete and EntityFieldQuery to get the users. Do that in an update hook.
    – webkenny
    Jan 29, 2015 at 1:38
  • Sounds like a plan then. I originally used the module: drupal.org/project/delete_all but I could not log in as admin after that. Even after several hours of various attempts including long calls with my host support. So I had to upload the backup db from that morning. That made me think, maybe easier to go to the db direct :p Have 9,000 spam bot users in 3 months. Ouch! Still cant figure out why what I see in phpMyAdmin looks different than in Drupal. Only one db in there... Any ideas? Jan 29, 2015 at 12:53

2 Answers 2


As mentioned in the comments, directly modifying the tables could be disastrous. If you need to get rid of large numbers of users I would suggestion Using Views with Views Bulk Operations:

You need Views if

You like the default front page view, but you find you want to sort it differently. You like the default taxonomy/term view, but you find you want to sort it differently; for example, alphabetically. You use /tracker, but you want to restrict it to posts of a certain type. You like the idea of the 'article' module, but it doesn't display articles the way you like. You want a way to display a block with the 5 most recent posts of some particular type. You want to provide 'unread forum posts'. You want a monthly archive similar to the typical Movable Type/Wordpress archives that displays a link to the in the form of "Month, YYYY (X)" where X is the number of posts that month, and displays them in a block. The links lead to a simple list of posts for that month.

This module augments Views by allowing bulk operations to be executed on the displayed rows. It does so by showing a checkbox in front of each node, and adding a select box containing operations that can be applied. Drupal Core or Rules actions can be used.

  • Thanks for the guidance Geoff! I will try and figure out how to use it. I've only played with Views a little bit. Jan 29, 2015 at 12:55

To answer your second related question, showing records with 30 per page is phpMyAdmin's default behaviour and you can always increase this number or have it show all records.

  • Yes. Thank you. I increased it to 1000 and same result. In the case of rid, still only see 3 role id's. Administrator, authenticated user, anonymous user. I have three other roles defined and they do appear in drupal. Any ideas? Jan 29, 2015 at 12:48
  • I've just checked on my own site, and all the roles appear under the table {role}.
    – Kartagis
    Jan 29, 2015 at 14:25
  • Humm...all I have is drup_role. There is no table "role". Same, I have drup_user which displays 8 of my 9000 spam users :p Jan 29, 2015 at 15:12
  • That {role} I mentioned is drup_role in your case.
    – Kartagis
    Jan 29, 2015 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.