While restoring or resetting password you need to ensure a random password always gets generated

for the user every time the request is made.

For doing that we always write a stored procedure to check first that the email id user is entering is registered or not.

After doing that password gets generated and sent to user's email id.

ALTER PROC [dbo].[uspForgotPassword]
@Email VARCHAR(50),
@Password VARCHAR(50),
@PasswordSalt VARCHAR(50)
AS

DECLARE @EncryptedPassword as VARBINARY(MAX);     
SELECT @EncryptedPassword = ENCRYPTBYPASSPHRASE (@PasswordSalt, @Password);    

UPDATE [dbo].[User] 
SET [Password] = @EncryptedPassword,
    [PasswordSalt] = @PasswordSalt

WHERE Email = @Email

Here we first get the password then encrypt it and send this  password  to the user's email