登录页面的存储过程 [英] Stored proceudre for login page

问题描述

我在sqlserver中创建了登录表，其中添加了用户名和密码.为此，如何编写存储过程.

问候
Balamurugan

Hi,
I have created the login table in sqlserver in that i have added user name and password.for this how to write stored procedure.

Regards
Balamurugan

推荐答案

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

这篇关于登录页面的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！