Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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
Posted 25-Sep-12 23:22pm
Comments
Abhijit Parab at 26-Sep-12 5:26am
   
Hi,
Try something from your end. If dont know how to write store procedure then google it or do RnD on it. If your getting problem then post code here.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
v2
Comments
vijay bisht at 26-Sep-12 6:43am
   
:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 530
1 mhegazy94 460
2 Ravi Bhavnani 230
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,205
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,644
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 26 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100