Login procedure for SQL

Nandha Kumar Nagarajan

7y977

How to create login procedure for sql server and check the first time login???

Help me

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anoop Kumar Sharma7y ago
Hi Nandhakumar,

Create a column i.e. LastLoginDate in your table and use that column in order to verify whether the user has logged in earlier to the system or not. On user creation, set its value as null and on successful login check that column value. If it's null then treat it as the first-time login. If passed Username and password is matched make sure to update this column with the current/logged in DateTime.

Note: If you are working in .Net framework and using membership then you can find LastLoginDate in the aspnet_membership table.

Hope this will help you.
0
Rupesh Kahane7y ago
Suppose your table contains the column value i.e LoginTime (User login time has been update whenever user logged into the system)
So on that basis you can track user has been first time login into the system or not.
Check below query & make it as a procedure
Declare
@UNAME VARCHAR(100) = 'Sam',
@PASS VARCHAR(100) = '123'

DECLARE @TBL_LOGIN TABLE
(
USERNAME varchar(20)
,PASWORD  varchar(20)
,LoginTime dateTime
,Is_Delete int not null default 0
)

Insert Into @TBL_LOGIN values('Sam','123',GETUTCDATE(),0)
Insert Into @TBL_LOGIN values('Rupesh','456',NULL,0)
Insert Into @TBL_LOGIN values('Rajesh','987',DATEADD(mi, 10,   GETUTCDATE()) , 0)
Insert Into @TBL_LOGIN values('Amol','321',DATEADD(mi, -10,   GETUTCDATE()) , 1)
Insert Into @TBL_LOGIN values('Shashi','321',NULL , 1)
--Select * from @TBL_LOGIN where Is_Delete = 0
IF Exists((SELECT Top 1 * FROM @TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS AND Is_Delete = 0))
begin
SELECT top 1 * FROM @TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS AND Is_Delete = 0
end
ELSE
begin
SELECT 'User has not yet logged in'
END
0
Amit Kumar7y ago
Hi,
In you db table ipAddress column is available it should null by default.when user is login then set is Value with ip address.
Create procedure proc_login
( @userid varchar (100),
@password varchar (100)

)

AS BEGIN
If exists(select 1 from tbllogin where udenrid=@userid and password =@password and ipAddress is null)
BEGIN
         Select 'user firsttime login'
END
          else 'User is not used'
END
0