14

I have to change a password to something else, I have got all the details like userid, username, encrypted password, password format.

How can I change the password via SQL in asp.net membership?

Mr A
  • 6,448
  • 25
  • 83
  • 137
  • 2
    Check out this post: http://stackoverflow.com/questions/287320/how-do-you-change-a-hashed-password-using-asp-net-membership-provider-if-you-don – E.J. Brennan Jan 24 '12 at 11:51
  • @E.J.Brennan the post you are referring to, is about resetting the password with C# code, not in SQL. – JP Hellemons Jun 08 '17 at 08:47

1 Answers1

42

You can use this query;

Declare @UserName NVarChar(30)    
Declare @Password NVarChar(30)    
Declare @Application NVarChar(255)    
Declare @PasswordSalt NVarChar(128)    

set @UserName = 'UserName'    
set @Password = 'Pass'    
set @Application = '/Application'    
Set @PasswordSalt = (SELECT TOP 1 PasswordSalt FROM aspnet_Membership WHERE UserID IN    (SELECT UserID FROM aspnet_Users u, aspnet_Applications a WHERE u.UserName=@UserName and a.ApplicationName = @Application AND u.ApplicationId = a.ApplicationId))    

Exec dbo.aspnet_Membership_ResetPassword @Application, @UserName, @Password, 10, 10, @PasswordSalt, -5
ssmith
  • 8,092
  • 6
  • 52
  • 93
oOZz
  • 657
  • 8
  • 18
  • 3
    This worked great - solves an inherited problem I've had for over 2 years! – MTAdmin Jan 28 '13 at 20:53
  • 10
    Please note this answer updates the user's password to a plain text password. This will not store a salted and hashed password. Note that the `set @PasswordSalt` statement does nothing here (and should read Select top 1 PasswordSalt FROM ....). – subsci Oct 02 '14 at 04:28
  • 2
    How can you make it to encrypt it? – Hrodger Mar 11 '16 at 15:31
  • 1
    My aspnet_Membership_ResetPassword requires 6 instead of 7 parameters: @application, @username, @newPassword, @passwordSalt, @currentTimeUtc, @passwordFormat. So can you explain the 10,10 and the -5? because passwordFormat seems to be always `1` – JP Hellemons Jun 08 '17 at 08:41
  • Never mind, I was looking at `aspnet_Membership_SetPassword` instead of `aspnet_Membership_ResetPassword`. My bad. – JP Hellemons Jun 08 '17 at 08:52