Unlock User or Reset Password via Database Query – ASP.NET Membership

This morning, I was logging into my website and couldn’t log in. My personal site uses the out-of-the-box ASP.NET v2 membership and roles. It took a while to determine what was wrong because my own website didn’t tell me much, using a blanket unsuccessful message for any problem. This led me to believe my password was wrong or worse that my site had been hacked and the password changed!

It turned out I entered the wrong password too many times and locked myself out. However, my site wasn’t programmed to tell me I was locked out (see here for improvement). I probably entered the right password loads of times, but couldn’t tell because my account was locked. Once I figured this out, the easiest way to unlock the user was via the SQL query window as my site is deployed on an ISP. You can unlock programmatically, but I wasn’t sure how to via the database directly. Luckily, a quick look through the sprocs revealed what I was looking for and the day was saved:

DECLARE @return_value int

EXEC @return_value = [dbo].[aspnet_Membership_UnlockUser]
@ApplicationName = N‘applicationName’,
@UserName = N‘user’

SELECT ‘Return Value’ = @return_value

GO

If you don’t know your application name, the query below can be handy. If you need to reset your password, you can use the information obtained by this query along with the sproc below. First, create a new user or you can use an existing user with a known password. Next, execute the query below:

SELECT au.username, aa.ApplicationName, password, passwordformat, passwordsalt
FROM aspnet_membership am
INNER JOIN aspnet_users au
ON (au.userid = am.userid)
INNER JOIN aspnet_applications aa
ON (au.applicationId = aa.applicationid)

Now that you have a valid password, salt, and password type, you can set that password information to the account which needs to be reset. So take the valid password, salt, and password format and put it in the sproc below along with the application name and the user which needs to be reset.

–Prepare the change date
DECLARE @changeDate datetime
set @changeDate = getdate()

–set the password
exec aspnet_Membership_setPassword ‘applicationName’, 
‘user’, 
‘password’, 
‘passwordsalt’,
@changeDate, 
Passwordformat

Execute. Now both users have the same password. Good luck!

References

• http://aquesthosting.headtreez.com/doc/b873561c-ab7a-4a8e-9934-cc9366af8a81
• http://mitchelsellers.com/Blogs/tabid/54/EntryID/23/Default.aspx
• http://msdn.microsoft.com/en-us/library/system.web.security.membershipuser.unlockuser.aspx