Introduction
In this article we will show you how to create Stored Procedures for Login Form in MS SQL Server 2012 based on below flow chart.
Visual Studio 2010/2012/2013/15/17, SQL Server 2005/08/2012
Project used Version
Visual Studio 2013, SQL Server 2012
Create Stored Procedures in MS SQL Server 2012
Create New Store procedure
spAuthentication Go to, DBLoginSystem -> Programmability -> Stored Procedures
Right Click and Create new Stored Procedure as shown below:
You will see pre-defined template as shown below.
Just delete everthing in pre-defined template, copy the follwing script and paste it.
SQL Script: Store Procedure Authentication
USE [DBLoginSystem]
GO
/****** Object: StoredProcedure [dbo].[spAuthentication] Script Date: 01-12-2018 8.58.15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spAuthentication] (@Email varchar(50),
@Password varchar(50),
@Result varchar(100) OUTPUT,
@Role varchar(50) OUTPUT,
@userName varchar(50) OUTPUT)
AS
BEGIN
DECLARE @isActivated bit,
@isReAttempLoked bit,
@Count int,
@RetryCount int,
@RoleId int,
@UserInfo varchar(50);
SET NOCOUNT ON;
IF EXISTS (SELECT
*
FROM Users
WHERE Email = @Email)
BEGIN
--select @Result = 'Valid Credential';
SELECT
@isActivated = isActivated,
@RoleID = RoleID,
@UserInfo = UserName
FROM Users
WHERE Email = @Email
IF (@isActivated = 0)
BEGIN
SELECT
@Result = 'Your Account is locked by Admin.',
@Role = '',
@userName = '';
END
ELSE
BEGIN
--select @Result = 'Unlocked';
SELECT
@isReAttempLoked = isLocked
FROM Users
WHERE Email = @Email
IF (@isReAttempLoked = 1)
BEGIN
SELECT
@Result = 'Account has been locked by invalid credential attempt.',
@Role = '',
@userName = '';
END
ELSE
BEGIN
SELECT
@Count = COUNT(Email)
FROM Users
WHERE ([Email] = @Email)
AND [Password] = @Password
IF (@Count = 1)
BEGIN
-- Reset RetryAttempts
UPDATE Users
SET RetryAttempts = 0,
LastLogin = GETDATE()
WHERE Email = @Email
SELECT
@Result = 'Logged Successfully!',
@Role = (SELECT
RoleName
FROM Roles
WHERE RoleID = @RoleID),
@userName = @UserInfo;
END
ELSE
BEGIN
-- If a match is not found
SELECT
@RetryCount = ISNULL(RetryAttempts, 0)
FROM Users
WHERE Email = @Email
SET @RetryCount = @RetryCount + 1
IF (@RetryCount <= 3)
BEGIN
-- If re-try attempts are not completed
UPDATE Users
SET RetryAttempts = @RetryCount
WHERE Email = @Email
SELECT
@Result = 'Invalid Password, No of attemps ' + CONVERT(varchar(50), @RetryCount) + ' out of 3.',
@Role = '',
@userName = '';
END
ELSE
BEGIN
-- If re-try attempts are completed
UPDATE Users
SET RetryAttempts = @RetryCount,
IsLocked = 1,
LockedDateTime = GETDATE()
WHERE Email = @Email
SELECT
@Result = 'Your account is locked.',
@Role = '',
@userName = '';
END
END
END
END
END
ELSE
BEGIN
SELECT
@Result = 'Invalid email Id.',
@Role = '',
@userName = '';
END
END
GO
Understanding Concept
Check Email against database if false returns "Invalid Email Id".
We have an Admin control panel to control user accounts.
If user account is blocked then it returns "Your account has been blocked by Admin. Please contact Administrator".
If user account is locked due to invalid credential attempts then it returns "Account has been locked by invalid credential attempts."
If Email Id and Password not matched against Database then
Retry count increase by 1
If Retry Count > 3 then (Invalid credential Attempts)
User Account will be locked automatically and returns "Your account has been locked by invalid credential attempts".
If Email Id and Password matched against Database then
Retry count = 0 (Clears previous invalid attempts)
returns "Logged Successfully"
If Role is Admin then Populate Admin Form
else If Role is Employee then Populate Employee Form.
Download Complete Source Code C#