How do I reset administrator Role permissions

Question

If you have managed to loose your role permissions, like this: How do I reset Magento Admin User Role

Answer 1

Note:- Use mysqldump to back up your database before doing anything further, As it will change the database values and will not be recoverable.

Run this sql command to reset role with id 1 to all permissions:

REPLACE INTO `authorization_rule` 
    (`rule_id`, `role_id`, `resource_id`, `privileges`, `permission`) 
VALUES (1, 1, 'Magento_Backend::all', NULL, 'allow')

Run this if you have deleted the Administrator group:

REPLACE INTO authorization_role 
    (`role_id`, `parent_id`, `tree_level`, `sort_order`, `role_type`, `user_id`, `user_type`, `role_name`)
VALUES (1, 0, 1, 1, 'G', 0, '2', 'Administrators')

Note the use of REPLACE, this will delete any existing. So be mindful of that.

Lastly you need to make sure your user have the correct role:

REPLACE INTO authorization_role 
    (`role_id`, `parent_id`, `tree_level`, `sort_order`, `role_type`, `user_id`, `user_type`, `role_name`)
VALUES 
    (2, 1, 2, 0, 'U', <insert your user id here>, '2', <insert user name here>)

You can find your user id by running:

SELECT `user_id`, `username` FROM `admin_user`;