How to fix "can't access Wordpress admin (/wp-admin) after changing url or migrating database"

Every once in a while, I get the horrible misfortune opportunity to fix a bug that is very small but very annoying. This one took an entire Sunday afternoon.

If you are ever migrating a Wordpress site across servers, and you did a database dump, and you think you renamed the tables correctly, and you CAN login using the old admin credentials on the new site but CAN'T access the Wordpress admin console (yourblog.com/wp-admin), then make sure that the below three queries are correct.

More specifically, your user needs to 1) be an administrator in the DB 2) have level 10 3) the wp_user_roles option needs to be set correctly in the wp_usermeta table.

Re: the third query: sometimes, if your original database had a different prefix i.e. old: wp_site, new: wp_ then the entry in wp_options will NOT be valid (option_name will be wp_site_user_level, not wp_user_level) and your admin will be able to login but will not be able to see /wp-admin. Fix by changing the prefix in the database.

Queries below:

SELECT meta_value FROM wp_usermeta WHERE user_id =[admin user_id] AND meta_key = 'wp_capabilities';

// ^ Should return something like this:

| meta_value                      |
+---------------------------------+
| a:1:{s:13:"administrator";b:1;} |
+---------------------------------+

AND

SELECT meta_value FROM wp_usermeta WHERE user_id =[admin user_id] AND meta_key = 'wp_user_level';

// ^ Should return 10

+------------+
| meta_value |
+------------+
| 10         |
+------------+

AND

SELECT option_name, option_value FROM wp_options WHERE option_name = 'wp_user_roles';

// ^ Should return 1 row, with a massive hash.

Hope this saves someone a Sunday afternoon :)