WordPress Database – User Roles Tied To Table Preface

If you are recreating a website and need to change the table names, one thing to keep in mind is that the user roles (plus their capabilities) in the wp_options table is saved under the option_name of wp_user_roles. Sounds simple, right? But what if you were not prefacing your WordPress tables with wp_, but with wps_ or even mysite_?

Say your WordPress tables are prefaced with wps_ (ie. wps_options, wps_users), your user roles are saved using the option_name of wps_user_roles. To see the user roles, if you have access to your WordPress database via phpMyAdmin for example, you can query it with SELECT * FROM wpo5_options WHERE option_name like '%user_roles%';

Why is this worth knowing? If you have a site set up but need to, for some reason, change what the tables are prefaced with, you will lose the the user roles that were set up if you don’t update the wp_options table to use the right name for the roles. Well, it won’t be lost. It’ll be misplaced until you update it manually like UPDATE wps_options SET option_name = 'wps_user_roles' WHERE option_name = 'wp_user_roles';

I came across this scenario, and found very little online about it, while manually extracting sites out of an old WordPress multisite and setting them up as single WordPress sites. Some of the sites we rebuilt and didn’t need to do this with, but a few of them had a lot of data that needed to be pulled out. The WordPress