How to verify that the user's mailbox is real in the member system _mssql

In the development of the site, we need to check and verify the user's registered mailbox, the user filled out the mailbox is a valid mailbox.

OK, we start with the database, modify the user table so that users have to fill out the email field, added 2 fields:

ALTER TABLE [dbo]. [Users]
ADD [Email] VARCHAR (MB) null, 
[isverify] BIT not NULL DEFAULT (0)
 SELECT * FROM [dbo].[ Users]

Because you need to do 2 functions, one is to require users to verify the validity of the mailbox, but also can be a mailbox to allow users to modify the user password. So you need to create a table to store these 2 types of data:

CREATE TABLE [dbo]. [Requestactiontype]
(
 [Type] NVARCHAR (2) NOT null PRIMARY KEY,
 [Description] NVARCHAR (a) null
)
go
INSERT into [dbo].[ Requestactiontype] ([type],[description])
VALUES (' V ', N ' verifies that the mailbox is valid. '),
(' C ', N ' user modifies password ')

Next, you also need to create another table, is to remember the user requested events, record the user's some information, such as account number, mailbox, Link has timeliness, etc.:

CREATE TABLE [dbo]. [Userrequestaction] (
 [Type] NVARCHAR (2) not NULL FOREIGN KEY REFERENCES [dbo]. [Requestactiontype] ([Type]),
 [Token] [uniqueidentifier] NOT NULL DEFAULT (NEWID ()),
 [account] [nvarchar] (a) not null,
 [Email [nvarchar] (NOT null,
 [Expire] [datetime] NOT NULL DEFAULT (DATEADD (Day, (1), Current_timestamp
)) Go
Source Code

When a user changes the mailbox successfully, it needs to be changed to False for [isverify]. So you need to write a trigger for the first table:

CREATE TRIGGER [dbo]. [Tri_users_update] 
on [dbo]. [Users]
For UPDATE
as
DECLARE @U_nbr NVARCHAR (), @IsVerify BIT
DECLARE @old_email VARCHAR (MB), @new_email VARCHAR
Select @new_email = [email]
from INSERTED Select @U_nbr = [U_NBR], @old_email = [email], @IsVerify = [I Sverify] from DELETED
IF @IsVerify = 1 and (LEN (ISNULL (@new_email, ')) = 0 OR @new_email <> @old_email)
 UPDA TE [dbo]. [Users] SET [Isverify] = 0 WHERE [U_NBR] = @U_nbr
go
Source Code

When a user issues an authentication mailbox or changes a password, let the program execute the following stored procedure:

CREATE PROCEDURE [dbo]. [Usp_userrequestaction_request]
(
 @Type NVARCHAR (2),
 @U_nbr NVARCHAR
) as IF not
EXISTS (SELECT top 1 1 from [dbo].[ Users] WHERE [U_NBR] = @U_nbr)
BEGIN
 RAISERROR (N ' account error or not exist, contact your system administrator. ', 16,1 return-end
DECLARE @Email NVARCHAR (MB)
SELECT @Email = [Email] FROM [dbo].[ Users] WHERE [U_NBR] = @U_nbr
IF EXISTS (SELECT top 1 1 from [dbo].[ Userrequestaction] WHERE [Type] = @Type and [Account] = @U_nbr and [Email] = @Email)
 UPDATE [dbo].[ Userrequestaction] SET [Token] = NEWID (), [Expire] = DATEADD (day, (1), current_timestamp) WHERE [Type] = @Type and [account] = @U_nbr and [Email] = @Email
ELSE
 INSERT into [dbo].[ Userrequestaction] ([Type],[account],[email]) VALUES (@Type, @U_nbr, @Email)
go
Source Code

The user verifies the mailbox validity, is after the login to carry on, therefore only needs to click "Verifies" the Ammonium button, the system namely sends the authentication mail to the user's mailbox.
In addition, when the user forgets the password, it is not under the login system, so you need to enter the user's account to proceed to the next step.

is to use this stored procedure [dbo]. [Usp_userrequestaction_request].

The next process is that the user opens his mailbox and looks up the message that was just sent by the system. Mail content is to see the actual needs, such as prompting the user, is not their own operation, security, etc., these are not how important, it is important that a link.

Instructs the user to click on the link. This link leads to a page on the site. When this page, the system will be in this page some program processing, check the validity of the link, whether time expires, if all is not a problem, will be updated isverify field ture.

If the user forgot the password, the user clicks on the link, the system will also check the validity, there is no period, face will change the password form, let the user to change the new password.

Ok, 2 more stored procedures, the first is to update the Isverify field values:

CREATE PROCEDURE [dbo]. [Usp_users_updateisverifyfield]
(
  @token NVARCHAR
) as
IF EXISTS (SELECT 1 1 from [dbo].[ Userrequestaction] WHERE [Token] = @token and [Expire] >= current_timestamp)
BEGIN
  DECLARE @Account NVARCHAR (
  SELECT @Account = [Account] FROM [dbo].[ Userrequestaction] WHERE [Token] = @token
  UPDATE [dbo].[ Users] SET [isverify] = 1 WHERE [U_NBR] = @Account
  UPDATE [dbo].[ Userrequestaction] SET [Expire] = DATEADD (day,-1,current_timestamp) WHERE [Token] = @token
End Go Source Code

The other is ResetPassword, resetting the password:

CREATE PROCEDURE [dbo]. [Usp_users_resetpassword]
(
  @token NVARCHAR (),
  @Password NVARCHAR) as
IF EXISTS (SELECT top 1 1 from [dbo].[ Userrequestaction] WHERE [Token] = @token and [Expire] >= current_timestamp)
BEGIN
  DECLARE @Account NVARCHAR (
  SELECT @Account = [Account] FROM [dbo].[ Userrequestaction] WHERE [Token] = @token
  DECLARE @pwd VARBINARY (MAX) = Encryptbypassphrase (' insus#sec!%y ', @ Password)
  UPDATE [dbo].[ Users] SET [PWD] = @pwd WHERE [U_NBR] = @Account 
  UPDATE [dbo].[ Userrequestaction] SET [Expire] = DATEADD (day,-1,current_timestamp) WHERE [Token] = @token
end
ELSE
BEGIN
  RAISERROR (N ' cannot change password, please contact customer service or network administrator. ', 16,1, return-end
Source Code

Database development in this way, the procedural aspects of watching you play yourself.

The above is a small set up to everyone to introduce the verification of the user's mailbox is the real existence of the method, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!