Here's a basic SQL script that I use for this:
1 CREATE TABLE dbo.aspnet_Users_PasswordResetList
2 (
3 ResetID uniqueidentifier NOT NULL,
4 UserID uniqueidentifier NOT NULL,
5 EmailAddress varchar(50) NOT NULL,
6 DateAdded datetime NOT NULL CONSTRAINT DF_aspnet_Users_PasswordResetList_DateAdded DEFAULT (GetUtcDate()),
7 DateLastSent datetime NOT NULL CONSTRAINT DF_aspnet_Users_PasswordResetList_DateLastSent DEFAULT (GetUtcDate()),
8
9 CONSTRAINT PK_aspnet_Users_PasswordResetList PRIMARY KEY NONCLUSTERED (ResetID),
10 CONSTRAINT UX_aspnet_Users_PasswordResetList_User UNIQUE NONCLUSTERED (UserID),
11 CONSTRAINT FK_aspnet_Users_PasswordResetList_User FOREIGN KEY (UserID)
12 REFERENCES dbo.aspnet_Users (UserID) ON UPDATE CASCADE ON DELETE CASCADE
13 )
14 GO
15 CREATE CLUSTERED INDEX IX_aspnet_Users_PasswordResetList_User ON dbo.aspnet_Users_PasswordResetList
16 (
17 UserID,
18 DateAdded
19 )
20 GO
21
22 CREATE proc dbo.aspnet_Membership_LogPasswordReset
23 (
24 @ApplicationName nvarchar(256),
25 @UserName nvarchar(256)
26 )
27 As
28 BEGIN
29 DECLARE @UserID uniqueidentifier, @EmailAddress varchar(256), @Now datetime, @NoSend bit
30 DECLARE @ResetID uniqueidentifier, @DateLastSent datetime
31
32 SET NOCOUNT ON
33
34 SELECT TOP 1
35 @UserID = U.UserID,
36 @EmailAddress = M.Email
37 FROM
38 dbo.aspnet_Applications As A INNER JOIN dbo.aspnet_Users As U
39 ON A.ApplicationID = U.ApplicationID
40 INNER JOIN dbo.aspnet_Membership As M
41 ON U.UserID = M.UserID
42 WHERE
43 Lower(@UserName) = U.LoweredUserName
44 And
45 Lower(@ApplicationName) = A.LoweredApplicationName
46 And
47 M.IsApproved = 1
48 And
49 (M.Email Is Not Null And 0 != Len(M.Email))
50
51 If @UserID Is Not Null
52 BEGIN
53 SELECT TOP 1
54 @ResetID = ResetID,
55 @DateLastSent = DateLastSent
56 FROM
57 dbo.aspnet_Users_PasswordResetList
58 WHERE
59 UserID = @UserID
60
61 If @ResetID Is Null
62 BEGIN
63 SET @ResetID = NewId()
64
65 INSERT INTO dbo.aspnet_Users_PasswordResetList
66 (
67 ResetID,
68 UserID,
69 EmailAddress
70 )
71 VALUES
72 (
73 @ResetID,
74 @UserID,
75 @EmailAddress
76 )
77 END
78 Else
79 BEGIN
80 SET @Now = GetUtcDate()
81 SET @NoSend = CASE
82 WHEN @DateLastSent > DateAdd(minute, -5, @Now) THEN 1
83 ELSE 0
84 END
85
86 UPDATE
87 dbo.aspnet_Users_PasswordResetList
88 SET
89 EmailAddress = @EmailAddress,
90 DateLastSent = CASE @NoSend
91 WHEN 0 THEN @Now
92 ELSE DateLastSent
93 END
94 WHERE
95 ResetID = @ResetID
96 END
97 END
98
99 SELECT
100 @ResetID As ResetID,
101 @EmailAddress As EmailAddress,
102 IsNull(@NoSend, 0) As NoSend
103 END
104 GO
105 GRANT EXECUTE ON dbo.aspnet_Membership_LogPasswordReset TO aspnet_membership_role
106 GO
107
108 CREATE proc dbo.aspnet_Membership_ProcessPasswordReset
109 (
110 @ResetID uniqueidentifier,
111 @NewPassword nvarchar(128),
112 @NewSalt nvarchar(128),
113 @PasswordFormat int
114 )
115 As
116 BEGIN
117 DECLARE @UserID uniqueidentifier, @EmailAddress varchar(50)
118
119 SET NOCOUNT ON
120
121 If NullIf(@NewPassword, '') Is Null Or NullIf(@NewSalt, '') Is Null
122 BEGIN
123 RAISERROR('Password encryption failed!', 16, 1)
124 Return -1
125 END
126
127 SELECT
128 @UserID = UserID,
129 @EmailAddress = EmailAddress
130 FROM
131 dbo.aspnet_Users_PasswordResetList As R
132 WHERE
133 ResetID = @ResetID
134
135 UPDATE
136 dbo.aspnet_Membership
137 SET
138 Password = @NewPassword,
139 PasswordSalt = @NewSalt,
140 PasswordFormat = @PasswordFormat,
141 LastPasswordChangedDate = GetUtcDate(),
142 IsLockedOut = 0
143 WHERE
144 UserID = @UserID
145
146 DELETE
147 FROM
148 dbo.aspnet_Users_PasswordResetList
149 WHERE
150 ResetID = @ResetID
151
152 SELECT
153 @EmailAddress As EmailAddress
154 WHERE
155 NullIf(@EmailAddress, '') Is Not Null
156 END
157 GO
158 GRANT EXECUTE ON dbo.aspnet_Membership_ProcessPasswordReset TO aspnet_membership_role
When the user wants to reset their password, call dbo.aspnet_Membership_LogPasswordReset; it will return their email address (if available), a GUID for the reset request, and a flag indicating whether the reset message has been sent within the last five minutes. I use the flag to prevent the system from bombarding the user with reset emails; at most, it should send one every five minutes.
If the NoSend flag is false, the GUID can then be used to construct a reset link, which can be sent to the user's email address. When they click on the link, you should generate and encode/encrypt a new random password, and call dbo.aspnet_Membership_ProcessPasswordReset; if the reset request is found in the database, it will update the user's password, unlock their account, and return their email address. You can then use that address to send the new password to them.