If you’ve ever been locked out of the Magento admin, for whatever reason, and need to create a new user, you can do so by making some adjustments to the following sql script and running it inside phpMyAdmin, or command line (CLI). You will need to make some changes to this script, so please read below for more details.

The MySQL Script

[sql]LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;

SET @SALT = “ms”;
SET @PASSWORD = “password”;
SET @PASS = CONCAT(MD5(CONCAT( @SALT , @PASSWORD) ), CONCAT(“:”, @SALT ));
SET @FIRSTNAME = “firstname”;
SET @LASTNAME = “lastname”;
SET @EMAIL = “firstname@email.com”;
SET @USERNAME = “myusername”;
SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL;

INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at)
VALUES (@FIRSTNAME,@LASTNAME,@EMAIL,@USERNAME,@PASS,NOW(),0,0,1,@EXTRA,NOW());

INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name)
VALUES (1,2,0,’U’,(SELECT user_id FROM admin_user WHERE username = @USERNAME),@FIRSTNAME);

UNLOCK TABLES;[/sql]

How to Edit the Script

  • If you need to set the salt portion of your password, you can change the ms to the lowercase letters of your choosing. You should rarely have to do this, and usually you can leave this as is.
  • Change the @PASSWORD value to the password you would like to use. In this example I have it set to ‘password’. *Line 04*
  • Change the @FIRSTNAME value to the user’s first name. *Line 06*
  • Change the @LASTNAME value to the new user’s last name. *Line 07*
  • Change the @EMAIL value to the new user’s email address. *Line 08*
  • Change the @USERNAME value to the new username you wish to use. *Line 09*

Once you’ve made these changes you can just paste this script into the SQL section of phpMyAdmin, or run it via CLI. You should then be able to use the new user that you created to login to the Magento admin.

Summary

There are a few ways that you can add a new user in Magento when you currently do not have access to the admin. This is just one of the methods that I’ve used in the past. I will post another method in the future that requires only FTP access to the server, as well as an easy way to just reset an existing user’s password via MySql CLI and phpMyAdmin.