Creating a User Login System with SQL Server Stored Procedures

Introduction

user authentication is a crucial feature. Implementing a secure and efficient login system using SQL Server stored procedures ensures that user credentials are validated against a database. Below, we outline how to set up a UsersDetails table and a LoginUser stored procedure to manage user logins.

Step 1. Create the UsersDetails Table

First, define a table to store user credentials. This example includes columns for Username and Password. Adjustments can be made to include additional user information as needed.

USE DBname;
GO
-- Create the UsersDetails table if not exists
CREATE TABLE IF NOT EXISTS UsersDetails (
   UserID INT PRIMARY KEY IDENTITY(1,1),
   Username NVARCHAR(50) NOT NULL,
   Password NVARCHAR(50) NOT NULL
   -- Add additional columns as needed
);
GO

Step 2. Develop the LoginUser Stored Procedure

Next, create a stored procedure (LoginUser) that verifies user credentials against the UsersDetails table. This procedure accepts @Username and @Password parameters and outputs a @Status indicating the success or failure of the login attempt.

USE [DBname];
GO
/****** Object:  StoredProcedure [dbo].[LoginUser]    Script Date: 7/1/2024 3:29:21 PM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[LoginUser]
    @Username NVARCHAR(50),
    @Password NVARCHAR(50),
    @Status INT OUTPUT
AS
BEGIN
    SET @Status = 0; -- Default status to success
    IF EXISTS (SELECT 1 FROM UsersDetails WHERE Username = @Username AND Password = @Password)
        SELECT 'Login successful' AS [Message], @Status AS [Status];
    ELSE
    BEGIN
        SET @Status = 1; -- Set status to fail
        SELECT 'Invalid username or password' AS [Message], @Status AS [Status];
    END
END;

Step 3. Implementing the Login process

In your application, call the LoginUser stored procedure with the provided username and password. The stored procedure will return a message indicating whether the login was successful (@Status = 0) or unsuccessful (@Status = 1).

DECLARE @Username NVARCHAR(50) = 'example_user';
DECLARE @Password NVARCHAR(50) = 'example_password';
DECLARE @Status INT;
EXEC [dbo].[LoginUser] 
   @Username,
   @Password,
   @Status OUTPUT;

IF @Status = 0
   PRINT 'Login successful';
ELSE
   PRINT 'Login failed: Invalid username or password';

Conclusion

Implementing user authentication using SQL Server stored procedures provides a robust way to manage login functionality within your applications. Ensure to secure passwords properly using hashing techniques and validate user inputs to enhance security further.