The capabilities are stored as user meta data in a serialized array for each site.
The key as a regular expression would look like that:
'~' . $GLOBALS['wpdb']->base_prefix . '(\d+)_capabilities~'
So … get the user meta data, find the capability strings (they hold the roles), and compare the unserialized result with the role you want to find.
Then get the blog ID, the
(\d+) in the regex above, and you are done.
I think that is easier to understand with some code:
if ( ! function_exists( 'get_user_blogs_by_role' ) )
* Get all blog IDs where the user has the given role.
* @param int $user_id
* @param string $role
* @return array
function get_user_blogs_by_role( $user_id, $role )
$out = array ();
$regex = '~' . $GLOBALS['wpdb']->base_prefix . '(\d+)_capabilities~';
$meta = get_user_meta( $user_id );
if ( ! $meta )
return array ();
foreach ( $meta as $key => $value )
if ( preg_match( $regex, $key, $matches ) )
$roles = maybe_unserialize( $meta[$key] );
// the number is a string
if ( isset ( $roles[$role] ) and 1 === (int) $roles[$role] )
$out = $matches;
$blogs = get_user_blogs_by_role( 37, 'editor' );
var_dump( $blogs ); // Array ( 2, 14, 90 )