December 22, 2003 at 10:29 am
Hi there,
I'm trying to work out the best way of doing this. I basically have a login table with the following fields:
- user_id
- user_type
- user_active
- user_loginattempts
- user_username
- user_password
I would like a T-SQL script to return 6 columns.
- user_exists
- login_attempts
- login_valid
- user_id
- login_active
- user_type
The "user_exists" column will say whether there is a matching username in the table. If this is the case then the "login_attempts" column should be returned. If the username and password is matched in the table then the "login_valid" should be true and the last three columns retrieved. If the username and password is not matched then "login_valid" should be false and the other fields "NULL"
This is what I have so far:
CREATE PROCEDURE usp_check_login
@username varchar(20),
@password varchar(20)
AS
DECLARE @user_exists int
DECLARE @login_valid int
DECLARE @user_id int
DECLARE @login_active int
DECLARE @login_attempts int
DECLARE @user_type int
SET NOCOUNT ON
SELECT @user_exists = COUNT(*), @login_attempts = user_loginattempts
FROM tbl_user
WHERE user_username = @username
SELECT @login_valid = COUNT(*)
FROM tbl_user
WHERE user_username = @username
AND user_password = @password
SELECT @user_id = user_id, @login_active = user_active, @user_type = user_type
FROM tbl_user
WHERE user_username = @username
AND user_password = @password
SELECT @user_exists AS 'user_exists',
@login_valid AS 'login_valid',
@user_id AS 'user_id',
@login_active AS 'login_active',
@login_attempts AS 'login_attempts',
@user_type AS 'user_type'
SET NOCOUNT OFF
GO
If anyone can help me out or point me in the direction of some tutorials that cover this, that would be great.
Thanks
Edited by - jkeepi on 12/22/2003 10:30:43 AM
December 22, 2003 at 11:21 am
I am using an empty resultset if user does not exist instead of returning a 0 in user_exists.
SELECT 1 AS t1.user_exists
, t1.user_loginattempts AS login_attempts
, CASE t2.user_id IS NULL THEN 0 ELSE 1 END AS login_valid
, t2.user_id
, t2.user_active AS login_active
, t2.user_type
FROM tbl_user t1
LEFT OUTER JOIN tbl_user t2
ON t1.user_id = t2.user_id
AND t2.user_username = @username
AND t2.user_password = @password
WHERE t1.user_username = @username
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Contributor to Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)
Articles: http://www.sqlservercentral.com/columnists/chedgate/
January 5, 2004 at 5:02 pm
jkeepi,
without knowing how you are storing the data it is a little hard to answer this correctly. However it looks like you may be adding records to the tbl_user table everytime someone logs in and that you don't have a table dedicated to just users. I would normalize this and do something along the lines of the following.
CREATE TABLE tbl_user
( userid int
, username varchar(20) CONSTRAINT PK_tbl_user PRIMARY KEY
, userpwd varchar(20)
, user_active bit
, user_type int
)-- create alternate key on userid
CREATE unique INDEX AK_tbl_user ON tbl_user(userid)
CREATE TABLE tbl_userlogins
( loginid int IDENTITY(1,1) CONSTRAINT PK_tbl_userlogins PRIMARY KEY
, userid int CONSTRAINT FK_tbl_user_tbl_userlogin FOREIGN KEY REFERENCES tbl_user(userid)
, logindate datetime
)
GO
CREATE PROCEDURE usp_check_login
( @username varchar(20)
, @password varchar(20)
, @user_exists bit = NULL OUTPUT
)
AS
SET NOCOUNT ON
-- Check to see if the user exists
IF EXISTS(SELECT * FROM tbl_user WHERE user_username = @username)
BEGIN
SET @user_exists = 1
END
ELSE
BEGIN
SET @user_exists = 0
RETURN
END -- Return the user info
SELECT login_valid = COUNT(loginid)
, u.user_active
, u.user_type
FROM tbl_user u
JOIN tbl_userlogin l ON u.userid = l.userid
WHERE user_username = @username
AND user_password = @password
GROUP BY u.user_active, u.user_type
SET NOCOUNT OFF
RETURN
GO
With this you would then be able to check the output variable to see if the user exists and if so then you could check the resultset to see if the login is valid (You won't have a record if it's not) and get the rest of the information.
Please note that I don't show how any of these tables are updated as I assume that is taken care of in another SP or two.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 6, 2004 at 4:39 am
Thanks for your insight gljjr.
I could use an output parameter but I am converting the output through a recordset an into XML. I therefore need all the info in the recordset. I've changed the SP and seems to be working nicely now:
CREATE PROCEDURE usp_CheckLogin
@username varchar(20),
@password varchar(20)
AS
DECLARE @user_exists bit
DECLARE @login_valid bit
DECLARE @login_active bit
DECLARE @login_attempts int
DECLARE @user_id int
DECLARE @user_type int
SET @user_exists = 0
SET @login_valid = 0
SET @login_active = 0
SET NOCOUNT ON
-- Check if username exists
IF EXISTS (SELECT user_id
FROM tbl_user
WHERE user_username = @username
}
BEGIN
SET @user_exists = 1
-- If username exists, get login attempts
SELECT @login_attempts = user_loginattempts
FROM tbl_user
WHERE user_username = @username
END
-- Check if login is valid
IF EXISTS (SELECT user_id
FROM tbl_user
WHERE user_username = @username
AND user_password = @password
}
BEGIN
SET @login_valid = 1
-- If login is valid get user details
SELECT @user_id = user_id, @login_active = user_active, @user_type = user_type
FROM tbl_user
WHERE user_username = @username
AND user_password = @password
END
-- Return fields
SELECT @user_exists AS 'user_exists',
@login_attempts AS 'login_attempts',
@login_valid AS 'login_valid',
@user_id AS 'user_id',
@login_active AS 'login_active',
@user_type AS 'user_type'
SET NOCOUNT OFF
GO
My table schema is:
CREATE TABLE [dbo].[tbl_user] (
[user_id] [int] IDENTITY (1, 1) NOT NULL ,
[user_type] [tinyint] NOT NULL ,
[user_active] [bit] NOT NULL ,
[user_loginattempts] [smallint] NULL ,
[user_username] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[user_password] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[user_namefirst] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[user_namelast] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[user_email] [nvarchar] (75) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply