An orphaned capability assignment is a role or capability record in wp_usermeta that no longer maps to a defined role in the active WordPress installation. The most common form is a stale wp_capabilities entry, but the same failure appears in wp_user_level rows, leftover wp_sitemeta keys on multisite, and serialized arrays that reference roles removed by a plugin or theme. For a small-to-mid publishing team, these orphans are not cosmetic. They change who can edit, approve, export, or delete content, and they survive plugin deactivations, role editor experiments, and manual database imports. This article covers the exact tables, the failure modes, the SQL and WP-CLI checks, and the cleanup sequence that does not break active editorial users.
Adjacent concepts here are user role resolution, capability meta keys, serialized option data, multisite user metadata, and the WP_User::has_cap() chain. The audience is a systems engineer or technical editor who already knows that wp_users stores identity and wp_usermeta stores per-user key-value data. The problem is not missing knowledge about the schema. The problem is that most audits stop at the wp_users table and never inspect the serialized capability arrays that actually control access.
Why Orphaned Capability Assignments Persist
WordPress does not garbage-collect user meta when a role disappears. If a plugin registers a custom role, assigns it to three editors, and is then deactivated, the wp_capabilities meta value for those three users still contains the custom role key. WordPress will ignore the unknown role during normal capability checks, but the stale key remains in the database. The same happens when a site owner uses a role editor to rename a role, when a theme registers a temporary role during setup, or when a staging database is merged into production with a different plugin set.
The second persistence vector is serialization. A wp_capabilities value looks like this:
a:1:{s:13:"administrator";b:1;}If a role key is removed from the active role list but the serialized array is not rewritten, the orphan survives as a string inside the meta value. A simple LIKE search for the role name will find it, but a naive REPLACE or UPDATE can corrupt the serialized length markers and make the entire meta value unreadable. That is the real failure mode: a well-intentioned cleanup that turns a stale role into a broken user object.
Where the Orphans Live
Start with the two tables named in the title. wp_users holds ID, user_login, user_email, user_status, and display_name. It does not hold roles. wp_usermeta holds umeta_id, user_id, meta_key, and meta_value. The capability-related keys are:
wp_capabilities— serialized array of role or capability names mapped to boolean values.wp_user_level— legacy numeric level, still written by some plugins and imports.wp_dashboard_quick_press_last_post_id— not capability-related, but often confused with user state during audits.wp_sitemetaon multisite — site-level meta that can hold role definitions for the network.
On a standard single-site install, the audit target is wp_usermeta where meta_key = 'wp_capabilities'. On multisite, each site has its own wp_usermeta table, and the network user record is shared. A user can have different roles on different sites, which means an orphan on site 2 may not be an orphan on site 3. The audit must be scoped per site table, not per user ID.
Step 1: Enumerate the Active Roles
Before touching the database, get the authoritative list of roles from the current codebase. The cleanest source is the wp_user_roles option in the wp_options table. Run this query:
SELECT option_value FROM wp_options WHERE option_name = 'wp_user_roles';The value is a serialized array of role slugs mapped to role definitions. Each role definition contains a name and a capabilities array. The role slugs are the keys you will compare against the wp_capabilities meta values. If you prefer WP-CLI, the equivalent is:
wp role list --fields=role,name --format=csvThis gives you the active role slugs without manually unserializing the option. The default roles are administrator, editor, author, contributor, and subscriber. Any role slug not in this list is a candidate orphan.
One caution: some plugins register roles conditionally. A role may exist only when a specific plugin is active, or only on certain pages, or only after a license check. If you audit a staging copy with the plugin disabled, you will see false positives. Run the audit on the production database, or on a staging copy that has the exact same plugin set active.
Step 2: Extract and Inspect the wp_capabilities Meta
Pull all capability meta rows into a readable form. The raw query is:
SELECT user_id, meta_value FROM wp_usermeta WHERE meta_key = 'wp_capabilities';Each meta_value is a serialized PHP array. You can unserialize it in a PHP script, or use a tool that understands PHP serialization. Do not attempt to parse it with a regular expression. The serialized format includes string length markers, and a role slug like editor is six characters, while administrator is thirteen. A regex that matches role names will also match substrings inside other serialized values.
For a quick manual check, copy the meta_value into a PHP one-liner:
php -r '$v = '''a:1:{s:13:"administrator";b:1;}'''; var_export(unserialize($v));'For a larger audit, write a small script that loops through the rows, unserializes each value, and compares the array keys against the active role list. The output should be a table of user_id, user_login, orphaned_role, and full_meta_value. That table is your cleanup queue.
Step 3: Identify the Orphan Types
Not every unknown key in wp_capabilities is an orphaned role. There are three distinct types:
Type 1: Removed Role Slug
The meta value contains a role slug that is not in wp_user_roles. Example: a:1:{s:16:"content_approver";b:1;} where content_approver was registered by a now-deactivated editorial workflow plugin. This is the classic orphan. The user may still have other valid roles, or the orphan may be the only role, leaving the user with no capabilities at all.
Type 2: Capability Key Without a Role
The meta value contains a capability name, not a role slug. Example: a:1:{s:13:"edit_others_posts";b:1;}. This is not a role assignment. It is a direct capability grant, which WordPress supports but rarely uses in normal operation. If the capability is no longer registered by any plugin, it is an orphaned capability. If it is still registered, it is a valid direct grant and should not be removed without understanding why it was added.
Type 3: Serialized Array with a Broken Length Marker
The meta value fails to unserialize. This is not an orphan in the strict sense, but it is a corrupted capability record. The user may be locked out of the admin, or WordPress may fall back to a default role. This type requires repair, not cleanup. The fix is to reconstruct the array from a known-good backup or to reset the user to a valid role.
Step 4: Check wp_user_level and Other Legacy Keys
wp_user_level is a numeric value from the pre-2.0 role system. It still appears in imports from old WordPress sites, in some membership plugins, and in hand-written SQL migrations. The value is not used by modern WordPress for capability checks, but it can confuse audits and some plugins still read it. If the wp_user_level value is higher than the user’s current role would allow, it is a stale assignment. Example: a user with the subscriber role and a wp_user_level of 10. The fix is to set wp_user_level to 0 or delete the meta row entirely.
Also check for plugin-specific capability keys. Some editorial workflow plugins store approval state in wp_usermeta under keys like approval_status, edit_lock, or workflow_state. These are not capability assignments, but they can block content if the plugin is deactivated and the meta remains. A full audit should list every meta_key that contains the substring cap, role, level, or approve, and then classify each one.
Step 5: Clean Up Without Corrupting Serialized Data
The safe cleanup sequence is:
- Back up the
wp_usermetatable. A full database backup is better, but a table-level export is the minimum. - For each orphaned role slug, remove only that key from the serialized array. Do not rewrite the entire array by hand.
- If the array becomes empty after removal, delete the
wp_capabilitiesmeta row. WordPress will assign the default role on the next user load. - If the array still contains at least one valid role, write the updated serialized array back to the database.
- For
wp_user_level, delete the meta row or set it to 0.
The PHP code for step 2 is straightforward:
$meta = get_user_meta( $user_id, 'wp_capabilities', true );
if ( is_array( $meta ) && isset( $meta['content_approver'] ) ) {
unset( $meta['content_approver'] );
if ( empty( $meta ) ) {
delete_user_meta( $user_id, 'wp_capabilities' );
} else {
update_user_meta( $user_id, 'wp_capabilities', $meta );
}
}Run this as a WP-CLI command or a small plugin file, not as a raw SQL UPDATE. The WordPress meta functions handle serialization correctly. A raw SQL REPLACE on a serialized string is the fastest way to break every user on the site.
If you must use SQL, the only safe approach is to select the row, unserialize it in a script, modify the array, serialize it again, and then run an UPDATE with the full new value. Never use REPLACE(meta_value, 'old', 'new') on serialized data.
Step 6: Verify the Cleanup
After cleanup, run the audit query again and confirm that no orphaned role slugs remain. Then test the affected users. Log in as each user, or use WP-CLI to check capabilities:
wp user get 42 --fields=ID,user_login,rolesIf a user had only the orphaned role, the roles field should now show the default role, usually subscriber. If the user had a valid role plus the orphan, the valid role should remain unchanged. Check the admin screens for the affected users: the Users list, the post editing screen, and any editorial workflow screens that depend on role checks.
One more verification: run a site-wide capability check for a few known actions. For example, confirm that an editor can still edit others’ posts, that an author can still publish, and that a subscriber cannot access the dashboard. The orphan cleanup should not change any of these outcomes. If it does, you removed a valid role or capability by mistake.
Preventing Future Orphans
The root cause is usually a plugin or theme that registers a role and then leaves it behind. Before deactivating any plugin that registers roles, export the current role list and the affected user meta. After deactivation, run the audit. If the plugin is part of a publishing workflow, document the role names and the users assigned to them. That documentation is the difference between a five-minute cleanup and a two-hour incident.
For teams that use staging and production databases, add a role audit to the deployment checklist. A staging merge can bring over wp_usermeta rows that reference roles from the staging plugin set. The audit query is cheap, and the cleanup script can be run as a WP-CLI command after every merge.
If the site uses a role editor plugin, treat every role rename as a data migration. Renaming a role in the plugin UI does not automatically update the wp_capabilities meta for existing users. The old role slug becomes an orphan, and the users lose the capabilities they had. The fix is to run a script that maps the old slug to the new slug and updates the meta before the rename is finalized.
What This Audit Does Not Cover
This audit covers wp_users and wp_usermeta only. It does not cover wp_options role definitions, wp_sitemeta network roles, or capability checks in custom code. It also does not cover user sessions, authentication cookies, or password resets. Those are separate failure modes with separate fixes. If a user is locked out after a cleanup, the cause is usually a broken serialized array, not a missing role. The repair path is to restore the wp_capabilities meta from backup and re-run the cleanup with the correct script.
For a related failure mode, see What to Fix First When a New WordPress Site Says Nothing Found. That article covers the case where a site appears empty after a migration or plugin change, which often shares the same root cause: stale or corrupted meta data that WordPress cannot interpret.
FAQ
How do I know if a role is orphaned or just inactive?
An orphaned role is a role slug that exists in a user’s wp_capabilities meta but not in the wp_user_roles option. An inactive role is a role that exists in wp_user_roles but is not assigned to any user. The audit in this article targets the first case. The second case is a separate cleanup task: removing unused role definitions from the wp_user_roles option.
Can I clean orphaned capabilities with a SQL query?
You can identify orphans with SQL, but you should not modify serialized meta values with SQL string functions. The safe path is to select the rows, unserialize them in PHP, remove the orphaned keys, and write the updated arrays back using update_user_meta(). A raw SQL REPLACE or UPDATE on a serialized string can corrupt the length markers and break the user’s capabilities entirely.
What happens if a user has only an orphaned role and no valid role?
WordPress will treat the user as having no capabilities. On the next user load, WordPress may assign the default role, usually subscriber, but this behavior depends on the code path. The user may be locked out of the admin until the wp_capabilities meta is repaired. The cleanup script in this article deletes the empty meta row, which triggers the default role assignment on the next load.
How often should a publishing team run this audit?
Run it after every plugin deactivation, every role editor change, every staging-to-production merge, and every major WordPress core update. For a small-to-mid publishing team with a stable plugin set, a monthly audit is enough. For a team that experiments with editorial workflow plugins, run it weekly or after every plugin change.