更新时间:2023-12-03 20:30:59
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,--incorrect login attempts
[LastIncorrectLoginAttemptsDate] [datetime] NOT NULL,-- last incorrect login attempt ate
[LoginLockStat] [char](1) NOT NULL,-- user login locked or not
[StatFlag] [char](1) NOT NULL, -- to indicate user live or not
[CrtdDt] [datetime] NOT NULL,
[CrtdBy] [varchar](10) NOT NULL,
[LstModDt] [datetime] NOT NULL,
[LastPasswordChangeDate] [datetime] NOT NULL,
[LstModBy] [varchar](10) NOT NULL,)
--Add primary to [UserID] column
--insert data into table
--login button click call store procedure here password store in the table in binary formate which is actually encrypted text.while checking for psw match send encrypted password to your login store procedure.
-- your login store procedure
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)
-- validate user access
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
-- validate Password expire
-- to check whether password expire or not.this is optional whether you want force to user change password after every month
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
-- =======================================================================================
-- Query to Validate User
-- =======================================================================================
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
Create proc SP_peerfilesharing
@L_Username nvarchar(50),
@L_Password nvarchar(50)
as
begin
Insert into Login values(@L_Username,@L_Password)
end
复制粘贴即可使用
copy paste this it will work