Unlock User or Reset Password via Database query – ASP.NET Membership February 13, 2010Posted by codinglifestyle in ASP.NET, CodeProject.
Tags: ASP.NET, membership, password, reset, roles, unlock, user
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
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’,
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