Show All Non-Subscriber Users in a WordPress Network

If you run a WordPress network (formerly multisite), you may have users with different roles on different blogs (e.g. Administrator on one, Editor on another, and Subscriber on a third). What if you have an upcoming upgrade and want to contact everyone who is more than a Subscriber on at least one blog? This SQL query will output their user ID, email, and display name:

SELECT wp_users.id,
       wp_users.user_email,
       wp_users.display_name
FROM   wp_usermeta
       JOIN wp_users
         ON wp_users.id = wp_usermeta.user_id
WHERE  wp_usermeta.meta_key LIKE '%_capabilities'
       AND wp_usermeta.meta_value != 'a:1:{s:10:"subscriber";b:1;}'
GROUP  BY wp_usermeta.user_id
ORDER  BY wp_users.user_email ASC;

Leave a Reply

Your email address will not be published.