In SQL while storing or retrieving passwords we need to ensure that it should be in encrypted format.

So for doing that we first need to store it in encrypted form

CREATE PROC [dbo].[uspRegisterUser]         
	@FirstName VARCHAR(50),    
	@LastName VARCHAR(50),
	@ProfileImage VARCHAR(500),
	@JobRoleID BIGINT,
	@Email VARCHAR(50),
	@CompanyID BIGINT,
	@Password VARCHAR(50),
	@PasswordSalt VARCHAR(50), 
	@AccessToken VARCHAR(50), 
	@CreatedDate DATETIME,
	@IsDeleted BIT,
	@DeletedDate DATETIME
  
AS      
IF NOT EXISTS (SELECT 1 FROM [dbo].[User] WHERE Email = @Email)      
BEGIN      
	DECLARE @EncryptedPassword as VARBINARY(MAX);    
	SELECT @EncryptedPassword = ENCRYPTBYPASSPHRASE (@PasswordSalt, @Password);    
     
	INSERT INTO [dbo].[User]
        ([FirstName]
        ,[LastName]
		,[ProfileImage]
        ,[JobRoleID]
        ,[Email]
        ,[CompanyID]
        ,[Password]
        ,[PasswordSalt]
        ,[AccessToken]
        ,[CreatedDate]
        ,[IsDeleted]
		,[DeletedDate])
    VALUES
        (@FirstName
        ,@LastName
		,@ProfileImage
        ,@JobRoleID
        ,@Email
        ,@CompanyID
        ,@EncryptedPassword
        ,@PasswordSalt
        ,@AccessToken
        ,@CreatedDate
		,@IsDeleted
        ,@DeletedDate)

		SELECT 1
END      
ELSE      
BEGIN      
		SELECT 2      
END 

This is the Stored procedure for registering a user

Password salt can be generated from code behind like this

string passwordsalt= Guid.NewGuid().ToString();

And when we authenticate user while login we write another stored procedure for it

ALTER PROC [dbo].[uspAuthenticateUser]
@Email NVARCHAR(100),        
@Password NVARCHAR(50),
@DeviceUUID VARCHAR(500), 
@DeviceType VARCHAR(150)
AS        
IF EXISTS(SELECT 1 FROM [dbo].[User] WHERE [Email] = @Email AND [IsDeleted] = 0)        
BEGIN        
	Declare @PasswordEncrypted NVARCHAR(MAX);        
	Declare @PasswordDecrypted VARCHAR(MAX);        
	Declare @PasswordSalt VARCHAR(50);        

	SELECT @PasswordEncrypted = [Password], @PasswordSalt = [PasswordSalt] FROM [dbo].[User] WHERE [Email] = @Email AND [IsDeleted] = 0 
	SET @PasswordDecrypted = CONVERT(VARCHAR(MAX), DECRYPTBYPASSPHRASE (@PasswordSalt, @PasswordEncrypted))  
	
	IF(@Password = @PasswordDecrypted)        
	BEGIN 
		

		SELECT [Email], FirstName + ' ' + LastName As FullName, [AccessToken],id FROM [dbo].[User] WHERE [Email] = @Email AND [IsDeleted] = 0
	END        
END