CREATE TABLE Usertable(
[Pk_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[UserID] [varchar](10) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Password] [binary](16) NOT NULL,
[IncorrectLoginAttempts] [smallint] NOT NULL,
[LastIncorrectLoginAttemptsDate] [datetime] NOT NULL,
[LoginLockStat] [char](1) NOT NULL,
[StatFlag] [char](1) NOT NULL,
[CrtdDt] [datetime] NOT NULL,
[CrtdBy] [varchar](10) NOT NULL,
[LstModDt] [datetime] NOT NULL,
[LastPasswordChangeDate] [datetime] NOT NULL,
[LstModBy] [varchar](10) NOT NULL,)
if OBJECT_ID('SPLogin') is not null
drop procedure SPLogin
Go
CREATE procedure SPLogin
(
@UserId varchar(10),
@Password binary(16),
@SocietyName varchar(10),
@AccPeriodDesc varchar(10)
)
as
Begin
Declare @LoginLockStat as char
Declare @InvalLoginAttmpt as int
set @InvalLoginAttmpt=(select IncorrectLoginAttempts from Usertable where UserID=@UserId)
if ((select LoginLockStat from Usertable where UserID=@UserId)='Y')
Begin
Raiserror('User access is Locked',16,1)
Return -1
End
if @InvalLoginAttmpt =3
Begin
begin tran
update Usertable
set IncorrectLoginAttempts =@InvalLoginAttmpt,
LoginLockStat ='Y',
LastIncorrectLoginAttemptsDate =GETDATE()
where UserID=@UserId
If @@RowCount=0 OR @@Error <>0
Begin
RaisError('Error while Updating data',16,1)
rollback
Return -1
End
commit
Raiserror('User access is Locked',16,1)
Return -1
End
if DATEDIFF (dd,convert(date,(select LastPasswordChangeDate from Usertable s where s.UserId like Upper(@UserId)),103),Getdate() )>=30
Begin
Raiserror('Password Expired',16,1)
Return -1
End
select * from SA_User_h
if exists(select null from Usertable
where UserID=@UserId
And Password=@Password
And StatFlag='L')
Begin
begin tran
update Usertable
set IncorrectLoginAttempts =0
where UserID=@UserId
If @@RowCount=0 OR @@Error <>0
Begin
RaisError('Error while Updating data',16,1)
rollback
Return -1
End
Commit
select 'success ful login'
End
Else
Begin
begin tran
update Usertable
set IncorrectLoginAttempts =@InvalLoginAttmpt+1,
LoginLockStat =(case when @InvalLoginAttmpt+1=3 then 'Y' else 'N' End ),
LastIncorrectLoginAttemptsDate =GETDATE()
where UserID=@UserId
If @@RowCount=0 OR @@Error <>0
Begin
RaisError('Error while Updating data',16,1)
rollback
Return -1
End
Commit
if ((select LoginLockStat from Usertable where UserID=@UserId)='Y')
Begin
Raiserror('User access is Locked',16,1)
Return -1
End
Else
Begin
Raiserror('Invalid Login or Password. Try Again',16,1)
Return -1
End
End
End