Listing authenticated users without roles

1st Jul
2008

Sometimes we'd like to list users who have registered with a Drupal site, but haven't been placed into any proper roles yet via subscriptions, purchases, membership approval or whatever.

Try this join query to get started:

  1. SELECT users.uid, name, created, access, login, status
  2. FROM {users}
  3. LEFT OUTER JOIN {users_roles}
  4. ON users.uid = users_roles.uid
  5. WHERE users_roles.uid IS NULL AND users.uid != 0

Comments

Came in handy for me.

Came in handy for me. Thanks.

Yes, I was wondering

I was wondering why this isn't in core, and even with the advanced user module it isn't possible to select/view users with only the role authenticated.

For example in one site, we would like to email all these users to remind them they should upgrade their account (buy a role) using for example mailout. However, mailout also only let's you select by custom roles.

I guess a workaround would be to grant new users a role anyway (with no rights) or use the above script to manually give all these users a role (with no rights) and go from there...