Last year when working with a new client I stumbled across this unique issue when trying to login to WordPress. There was no issue with the front end being displayed but anytime they tried to log in via /wp-admin/ or wp-login.php all that would be displayed is a dreaded “Sorry, you are not allowed to access this page” prompt.
Click here for the he TLDR answer otherwise keep reading to find out how I debugged the issue.
After countless hours of debugging and trying every WordPress trick in the book like disabling all plugins, resetting the .htaccess, and disabling the custom themes nothing seemed to bring back the wordpress backend. At that point I knew it had to be a result of some sort of database corruption or a faulty line of code so my next logical step was to move the database to a clean install WordPress file system within a local sandbox. Testing WordPress installs this way can can be difficult for novice developers because you must change the hostname manually in the database to match the hostname of your sandbox aka testing environment – more info about that here. Once I uploaded the database to the fresh WordPress file system I still got the same error when trying to access the login page which proved to me that it was 100% an issue with the database.
This discovery then led me to using phpMyAdmin to investigate which table of the database was responsible for causing the lockout of the WordPress backend. After careful consideration I determined that the wp_options table was most likely the culprit. I then changed the name of the wp_options table to wp_options_bak and copied the wp_options table from a fresh WordPress database to my broken database. Then BOOM! I was able to login again YAY! But it wasn’t time to celebrate just yet because the original wp_options table was full of settings that I was not going to manually set up again – aint nobody got time fo dat.
The other bad part was that the original broken wp_options table was 4000+ rows and I had to find out which row or cells were causing the issue. I then decided my best bet was to start off by only reviewing the rows that were provided with the fresh WordPress install and comparing them to what was in my broken wp_options table. After about an hour of close cross examination I noticed that the wp_user_roles row was missing from the broken websites database. Using phpMyAdmin, I then went ahead and added a new row with the stock WordPress database info listed below. After a refresh of the /wp-admin page BINGO, problem solved. Now we can celebrate!
The only thing that I could’ve imagined to cause this issue would be an interrupted upgrade of WordPress or a plugin that modifies the wp_user_roles cell such as the Woocommerce plugin.
To fix it, open phpMyAdmin then insert a new row with the data below into the wp_options table if no such row already exists:
Back up all of your data before making any changes!
You may need to replace ‘wp_’ with your custom db prefix.
[sql title=”option_name”]wp_user_roles[/sql]
[sql title=”option_value”]a:5:{s:13:"administrator";a:2:{s:4:"name";s:13:"Administrator";s:12:"capabilities";a:61:{s:13:"switch_themes";b:1;s:11:"edit_themes";b:1;s:16:"activate_plugins";b:1;s:12:"edit_plugins";b:1;s:10:"edit_users";b:1;s:10:"edit_files";b:1;s:14:"manage_options";b:1;s:17:"moderate_comments";b:1;s:17:"manage_categories";b:1;s:12:"manage_links";b:1;s:12:"upload_files";b:1;s:6:"import";b:1;s:15:"unfiltered_html";b:1;s:10:"edit_posts";b:1;s:17:"edit_others_posts";b:1;s:20:"edit_published_posts";b:1;s:13:"publish_posts";b:1;s:10:"edit_pages";b:1;s:4:"read";b:1;s:8:"level_10";b:1;s:7:"level_9";b:1;s:7:"level_8";b:1;s:7:"level_7";b:1;s:7:"level_6";b:1;s:7:"level_5";b:1;s:7:"level_4";b:1;s:7:"level_3";b:1;s:7:"level_2";b:1;s:7:"level_1";b:1;s:7:"level_0";b:1;s:17:"edit_others_pages";b:1;s:20:"edit_published_pages";b:1;s:13:"publish_pages";b:1;s:12:"delete_pages";b:1;s:19:"delete_others_pages";b:1;s:22:"delete_published_pages";b:1;s:12:"delete_posts";b:1;s:19:"delete_others_posts";b:1;s:22:"delete_published_posts";b:1;s:20:"delete_private_posts";b:1;s:18:"edit_private_posts";b:1;s:18:"read_private_posts";b:1;s:20:"delete_private_pages";b:1;s:18:"edit_private_pages";b:1;s:18:"read_private_pages";b:1;s:12:"delete_users";b:1;s:12:"create_users";b:1;s:17:"unfiltered_upload";b:1;s:14:"edit_dashboard";b:1;s:14:"update_plugins";b:1;s:14:"delete_plugins";b:1;s:15:"install_plugins";b:1;s:13:"update_themes";b:1;s:14:"install_themes";b:1;s:11:"update_core";b:1;s:10:"list_users";b:1;s:12:"remove_users";b:1;s:13:"promote_users";b:1;s:18:"edit_theme_options";b:1;s:13:"delete_themes";b:1;s:6:"export";b:1;}}s:6:"editor";a:2:{s:4:"name";s:6:"Editor";s:12:"capabilities";a:34:{s:17:"moderate_comments";b:1;s:17:"manage_categories";b:1;s:12:"manage_links";b:1;s:12:"upload_files";b:1;s:15:"unfiltered_html";b:1;s:10:"edit_posts";b:1;s:17:"edit_others_posts";b:1;s:20:"edit_published_posts";b:1;s:13:"publish_posts";b:1;s:10:"edit_pages";b:1;s:4:"read";b:1;s:7:"level_7";b:1;s:7:"level_6";b:1;s:7:"level_5";b:1;s:7:"level_4";b:1;s:7:"level_3";b:1;s:7:"level_2";b:1;s:7:"level_1";b:1;s:7:"level_0";b:1;s:17:"edit_others_pages";b:1;s:20:"edit_published_pages";b:1;s:13:"publish_pages";b:1;s:12:"delete_pages";b:1;s:19:"delete_others_pages";b:1;s:22:"delete_published_pages";b:1;s:12:"delete_posts";b:1;s:19:"delete_others_posts";b:1;s:22:"delete_published_posts";b:1;s:20:"delete_private_posts";b:1;s:18:"edit_private_posts";b:1;s:18:"read_private_posts";b:1;s:20:"delete_private_pages";b:1;s:18:"edit_private_pages";b:1;s:18:"read_private_pages";b:1;}}s:6:"author";a:2:{s:4:"name";s:6:"Author";s:12:"capabilities";a:10:{s:12:"upload_files";b:1;s:10:"edit_posts";b:1;s:20:"edit_published_posts";b:1;s:13:"publish_posts";b:1;s:4:"read";b:1;s:7:"level_2";b:1;s:7:"level_1";b:1;s:7:"level_0";b:1;s:12:"delete_posts";b:1;s:22:"delete_published_posts";b:1;}}s:11:"contributor";a:2:{s:4:"name";s:11:"Contributor";s:12:"capabilities";a:5:{s:10:"edit_posts";b:1;s:4:"read";b:1;s:7:"level_1";b:1;s:7:"level_0";b:1;s:12:"delete_posts";b:1;}}s:10:"subscriber";a:2:{s:4:"name";s:10:"Subscriber";s:12:"capabilities";a:2:{s:4:"read";b:1;s:7:"level_0";b:1;}}} [/sql]
