1st, I have to mention that, it’s very difficult to restore the lost WordPress admin permissions, if you are not familiar with SQL even as a beginner. It’s better to hire an experienced developer to help you in this case.
If you decide to try to fix the issue yourself, then let’s start.
I. Study, study and study
If you are familiar with SQL skip this step.
Understand a general purpose of SELECT and UPDATE SQL commands:
– SELECT is used to retrieve rows selected from one or more tables;
– UPDATE statement updates columns of existing rows in the named table with new values.
Try to follow at least one of available quick MySQL tutorials before begin work with a real data. Look at Guru99.com as a one of the good points to start.
You will need some tool to connect to your WordPress MySQL database and execute SQL commands. Look at the PhpMyAdmin or MySQL Workbench.
Warning! Create a fresh backup of your WordPress MySQL database. You should have one always before make any changes with your site data. It allows you to rollback your database to the previous state in case if something went wrong.
II. Does your user still have administrator role?
Check that user under which you login to your site still has the administrator role:
1) Get that user ID:
SELECT * FROM wp_users WHERE user_login='your_user_login';
replace ‘your_user_login’ at the command above with your own user login and remember ID field value from result you will find.
2) Find what capabilities are assigned to this user:
SELECT * FROM wp_usermeta WHERE user_id=NN AND meta_key='wp_capabilities';
where NN is a value from record ID field you found at the 1st step. Some WordPress installation has a changed table prefix, differs from the a default ‘wp_’. You can find it at wp-config.php file. Look at the line with this code:
$table_prefix = '...';
If your table prefix differs from a default ‘wp_’, then replace ‘wp_’ in all SQL commands to your own, like “AND meta_key=’sp2101_capabilities’ for table prefix ‘sp2101’.
Btw, change of default table prefix has sense just in case you setup more then 1 WordPress site using the same MySQL database. It does not really enhance your database security. Smart hacker always can use ‘SHOW TABLES’ SQL command in order to list all available database tables names. They are not hidden in any way from person who got access to the SQL command execution.
So I’m sure such change just make your own life more difficult, not a hacker life :). Compare if you need to type manually ‘select * from wp_options’ or ‘select * from sp123456ed_options’ after changing table prefix trying to following so-called security recommendation.
For default ‘admin’ user commands will be:
SELECT * FROM wp_users WHERE user_login='admin';
SELECT * FROM wp_usermeta WHERE user_id=1 AND meta_key='wp_capabilities';
and capabilities assigned to ‘admin user is a serialized PHP array: ‘a:1:{s:13:”administrator”;b:1;}’. It means that ‘admin’ user has the ‘administrator’ role. Write somewhere ‘umeta_id’ field value. This unique record ID may be useful later.
If you get another result of the ‘SELECT * from wp_usermeta’ query then your user lost his administrator role somehow. Use this UPDATE query in order to assign it back to him:
UPDATE wp_usermeta SET meta_value='a:1:{s:13:"administrator";b:1;}' WHERE umeta_id=NN LIMIT 1;
NN here is a numeric ‘umeta_id’ field value from the previous ‘SELECT * FROM wp_usermeta’ query I asked you to write for the later use.
If your user has the administrator role as he should then something wrong took place with ‘administrator’ role data. We can proceed with default user roles restore.
III. Restore default user roles
This post explains in details how and where WordPress stores user roles data (including ‘administrator’ one).
This article explains where you can get a backup copy of user roles on the moment you installed User Role Editor or default user roles created by WordPress.
So we have a step by step scenario to resolve our problem already:
1) Follow recommendations from the 3rd variant described here:
– find a backup copy of your user roles with this command:
SELECT * FROM wp_options WHERE option_name='wp_backup_user_roles';
You will need a data from the ‘option_value’ field later.
If your $table_prefix at wp-config.php differs from a default value ‘wp_’ then you should replace ‘wp_’ at all SQL commands to your own table prefix value.
2) Find you current user roles record with command:
SELECT * FROM wp_options WHERE option_name='wp_user_roles';
Remember record numeric unique ID: ‘option_id’ field value. It will be used in the UPDATE statement later.
3) Use the UPDATE statement to replace your current roles data with a data from the backup record: copy and paste them to this command:
UPDATE `wp_options` SET option_value='changed value here' WHERE option_id=NN LIMIT 1;
replace NN with actual user roles record ID.
Try to login now and check that you got back the access to the WordPress admin backend with a default administrator permissions. Now you need to proceed with restore of active plugins and permissions (roles and capabilities) they install during activation. For example, WooCommerce restores its lost roles and capabilities automatically when you reactivate it. Some plugins creates own roles just during fresh install. So you need deactivate and delete via WordPress such plugin. Then install it back in order to restore it’s roles automatically.
In case you have a lot of data which you can not lose – right written plugin deletes its data during plugin deletion via WordPress, install such plugin at development copy of a site and look what roles and capabilities it creates and re-create them manually at your site.
