Need to create a new admin user in mysql

[ElisabettaCarrara]

Expected behavior

Followed this guide:

Create a WordPress Admin User for A WordPress Site via MySQL

to insert a new admin in mysql after accidentally deleting the main one from simple WordPress membership dashboard.
The expected behavior is the ability to log in as the admin with the new user.
To create the new user I deleted the plugin from files and cleaned relevant database tables because it has its own registration/login pages and skips the cp standard process.

Current behavior

After the procedure, it logs in, as a subscriber with no dashboard access.

Steps to reproduce

Include code to reproduce, if relevant.

1. install Wordpress Simple Membership plugin and its free add-ons (recaptcha and the one to sync wp users with members)
2. sync members with wp registered users
3. try and delete the new members from the WordPress simple membership dashboard. It is going to delete also the registerd users on the site, not only their member status
4. delete all plugins files, delete mysql tables, clean mysql.
5. perform the steps in the tutorial mentioned above.

Context

I need to regain access to the dashboard. Is there something else I can try before reverting to a previous backup? Also what happens with the content I created before deleting the admin, is there a way to assign it to the new admin?

Thanks for helping me out!

[klein]

Copying possible solution from Slack:

Zulfikar (@zulfgani) [10:48 AM]
When inserting the meta_key in user meta make sure the prefix matches your database prefix. In the tutorial it states wp_capabilities however if your DB prefix is other than wp_ then it wont work

[ElisabettaCarrara]

tried that, didn’t solve.
it continues to log in as a user (subscriber I think).
Is there any place in mysql except for user and usermeta where cp handles users capabilities and roles?

[klein]

Adding entire Slack conversation for reference of things that has been tried.

View in #programming on Slack

@elisabetta.marina.cle: Big question about MySQL. I followed a tutorial to manually register a new admin (deleted the admin yesterday for lack of sleep… Lesson learned). Now as I try to login it redirects me as a standard user with no dashboard access
Was using this tutorial
https://www.tipsandtricks-hq.com/create-a-wordpress-admin-user-for-a-wordpress-site-via-mysql-6649

Tips and Tricks HQ: Create a WordPress Admin User for A WordPress Site via MySQL

What should I do to let MySQL understand this is admin?
I think the tutorial is ok.
BTW I cleaned dbase from the offending plugin and removed it from files

@pvnoort: Have you tried this?

The above worked till I tried to login and failed with: a:1:{s:13:”administrator”;s:1:”1″;}

My work around was to use this instead: a:1:{s:13:”administrator”;b:1;}

Thanks all the same!

@elisabetta.marina.cle: No. I will edit. Didn’t see that.
(I have done everything in a sleep deprivation status. Getting to it this morning after a good sleep)
What is the difference however? I have to understand what this string does… After solving I will try to study a bit MySQL…
Edited. Doesn’t work either.
Now, to the MySQL manual. Maybe in one of its pages there is the solution.

@pvnoort: I dont know, I just saw it in the comments
Thought it might be worth a try
Maybe open a support ticket on the forum?
That might get you better response.

@elisabetta.marina.cle: Uhm
Yes.
I tried also user level to 10
I think there should be something else

@zulfgani: When inserting the meta_key in user meta make sure the prefix matches your database prefix. In the tutorial it states wp_capabilities however if your DB prefix is other than wp_ then it wont work

@elisabetta.marina.cle: Ahhhh… Wait a minute
Ok. Gonna check that
I have to insert myprefix_capability
So now it’s clear
Nope. Doesn’t work either.

@zulfgani: myprefix_capabilities and not myprefix_capability

@elisabetta.marina.cle: Yes. I did it the right way in MySQL… :rolling_on_the_floor_laughing:

@zulfgani: Did you change the meta_value too? This is correct a:1:{s:13:"administrator";s:1:"1";} as per the tutorial

@elisabetta.marina.cle: I tried both suggested…
I am trying to think where the issue is.
Maybe shield security?
Could another plugin block my access as admin?

@pvnoort: you could try deactivating all plugins, see if that solves it.

@elisabetta.marina.cle: It has my ip in whitelist. So it’s strange. Ok let’s rename plugin folder.
Question, if it’s not that easy, why are there so many hackers doing exactly this?

@pvnoort: Oh btw, do you only refresh the page when you test solutions, or do you log out and re login?

@zulfgani: Gotta go attend my brick and mortar business but will check back later and look further into it if you’ve not found a solution

@elisabetta.marina.cle: Logout and login. I think it’s the best way to inform the site something has changed
@zulfgani thanks a lot.

@pvnoort: Then I am at the end of my suggestions, I am not a CP database expert unfortunately

@elisabetta.marina.cle: Let’s see if it’s a plugin.
Deactivated all of them
Nope
Ok. Gotta dig deeper. And study a solution.

[ElisabettaCarrara]

Solved by deactivating plugins (renamed plugins and shield folder in wp-content and following wpbeginners tutorial

How to Add an Admin User to the WordPress Database via MySQL

(that is totally the same as the first I was using)