Coding Lifestyle

Unlock User or Reset Password via Database query – ASP.NET Membership February 13, 2010

This morning I was logging in to my website and couldn’t log in.  My personal site uses the out-of-the-box ASP.NET v2 membership and roles.  This 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 lead 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 programatically, 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 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!

Ref: 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