SQL query to block all users belonging to a role

If you need to change the status of all users who belong to a specific role you can use this SQL query. There are various reason you might want to do this is, for example if you synch your production DB with staging, and users of a specific role are set to receive automated emails or something. 

Before you run this you will need to find out what the role ID is (rid). You can simply do this by going to the Drupal admin page listing all the roles (/admin/people/permissions/roles). Click the edit role link next to the relevant role and you will see it's rid in the page URL, eg. http://www.yoursite.com/admin/people/permissions/roles/edit/5

In the above example the rid is 5. Once you have found the rid, amend the ur.rid=5 in the code below to match yours.

UPDATE users AS u
INNER JOIN users_roles AS ur ON u.uid = ur.uid 
SET u.status=0
WHERE ur.rid=5;

Although the format of this query is for running via a SQL client like PHPMyAdmin, you could also run it through Drush using the sql-query command, ie: drush sql-query "..."

Drupal version: 
Tags: 
Justin Chevallier

Justin Chevallier

Avid Drupal site builder & user for +10 years.

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.