How to solve ‘You do not have sufficient permissions to access this page.’ in WordPress

I have been running into troubles logging in to WordPress and was kinda frustrated all morning.

Basically each time I use tried to log in, I get:

You do not have sufficient permissions to access this page.

I could not edit the blog, nothing works except the front page. I did a couple of searches at Google but the solutions didn’t seem to work for me.

In the end, I did a new installation of WordPress and scrutinized every damn thing in the users and usermeta table between a database that worked and a database that doesn’t.
Did you change the prefix of your table names in WordPress after the migration?

In my old database, I used ‘wp_’ as my prefix. As I migrated, I decide to use ‘wp3_’ instead of ‘wp_’. Unfortunately, the meta_key values are tied down to:

* wp_user_level
* wp_capabilities
* wp_autosave_draft_ids

I need to update it to

* wp3_user_level
* wp3_capabilities
* wp3_autosave_draft_ids

I wrote an SQL statement to share, just replace ‘prefix_’ with your new WordPress prefix. The following statements go to ‘meta_key’ and does a string replace from ‘wp_’ to ‘prefix_’.

UPDATE `prefix_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'wp_', 'prefix_' );

(Make sure you do backups first! Note that the quotes used for string and field name are different.)

In the options table, there is ‘wp_user_roles’, make sure you get that changed into ‘prefix_user_roles’.

UPDATE `prefix_options` SET `option_name` = 'prefix_user_roles' WHERE `option_name` ='wp_user_roles' AND `blog_id` =0;

(Make sure you do backups first! Note that the quotes used for string and field name are different.)

With everything set, I tried logging in again and it worked. A happy WordPress user once again. I am using WordPress 2.2.2 before the migration and I did not upgrade WordPress during the migration. Hope it helps. (Applies to WordPress 2.3.3 also.)