? regarding validating users with same last name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

? regarding validating users with same last name

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-22 : 09:08:39

lionchatta writes "I have a table with each person's last name and first name. I grab the users nt login information from nt challenge/response. the nt login consists of only the last name. so i could validate users easily this way, by running a select where lastname = loginname. although this fails when i have users with the same last name. when this occours we put their first intial of their first name to their last name. i.e. smith, smithb, smithr, smithro. but all last names are smith, so i need somway of grabbing parts of the firstname to check and validate these types of users. i can not figure out the way to begin to accomplish. any help would be greatly appreciated.

thanks"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-22 : 09:38:49

lastname + left(firstname,1) = loginname

<O>

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-22 : 09:52:34

Best thing to do is store loginname in the table. Since you are asking this question, I assume that is not possible.

So here is some code that will get you are partial answer:


set nocount on

create table #User (First_Name varchar(30) NOT NULL, Last_Name varchar(40) NOT NULL)

Insert #User (First_Name, Last_Name) Values ('Bill', 'Smith')
Insert #User (First_Name, Last_Name) Values ('Tom', 'Smith')
Insert #User (First_Name, Last_Name) Values ('Jim', 'Smithers')
Insert #User (First_Name, Last_Name) Values ('Mary', 'Allen')

declare @NtUserID varchar(30)

-- find user Mary Allen with a simple select statement
print '** find user Mary Allen with a simple select statement'
select @NtUserID = 'Allen'
Select * from #User where Last_Name = @NtUserID
print ''

-- find user Tom Smith with advanced logic
print '** find user Tom Smith with advanced logic'
select @NtUserID = 'SmithT'
Select * from #User 
where Last_Name = Left(@NtUserID, DataLength(Last_Name)) 
  and Left(First_Name, (Len(@NtUserID)-DataLength(Last_Name))) = 
         Right(@NtUserID, (Len(@NtUserID)-DataLength(Last_Name)))
print ''

-- find user Mary Allen with advanced logic
print '** find user Mary Allen with advanced logic'
select @NtUserID = 'Allen'
Select * from #User 
where Last_Name = Left(@NtUserID, DataLength(Last_Name)) 
  and Left(First_Name, (Len(@NtUserID)-DataLength(Last_Name))) = 
         Right(@NtUserID, (Len(@NtUserID)-DataLength(Last_Name)))
print ''


--now add user Tina Smith
print '** now add user Tina Smith'
Insert #User (First_Name, Last_Name) Values ('Tina', 'Smith')
print ''


-- find user Tina Smith with advanced logic
print '** find user Tina Smith with advanced logic'
select @NtUserID = 'SmithTi'
Select * from #User 
where Last_Name = Left(@NtUserID, DataLength(Last_Name)) 
  and Left(First_Name, (Len(@NtUserID)-DataLength(Last_Name))) = 
         Right(@NtUserID, (Len(@NtUserID)-DataLength(Last_Name)))
print ''

-- now find user Tom Smith with advanced logic (after Tina Smith has been added)
-- PROBLEM: this select returns both Tom Smith AND Tina Smith
print '** now find user Tom Smith with advanced logic (after Tina Smith has been added)'
print '** PROBLEM: this select returns both Tom Smith AND Tina Smith'
select @NtUserID = 'SmithT'
Select * from #User 
where Last_Name = Left(@NtUserID, DataLength(Last_Name)) 
  and Left(First_Name, (Len(@NtUserID)-DataLength(Last_Name))) = 
         Right(@NtUserID, (Len(@NtUserID)-DataLength(Last_Name)))
print ''


drop table #User

Here are the results:


** find user Mary Allen with a simple select statement
First_Name                     Last_Name                                
------------------------------ ---------------------------------------- 
Mary                           Allen

 
** find user Tom Smith with advanced logic
First_Name                     Last_Name                                
------------------------------ ---------------------------------------- 
Tom                            Smith

 
** find user Mary Allen with advanced logic
First_Name                     Last_Name                                
------------------------------ ---------------------------------------- 
Mary                           Allen

 
** now add user Tina Smith
 
** find user Tina Smith with advanced logic
First_Name                     Last_Name                                
------------------------------ ---------------------------------------- 
Tina                           Smith

 
** now find user Tom Smith with advanced logic (after Tina Smith has been added)
** PROBLEM: this select returns both Tom Smith AND Tina Smith
First_Name                     Last_Name                                
------------------------------ ---------------------------------------- 
Tom                            Smith
Tina                           Smith

The problem is, once you add a second user with the same last name,
the SQL statement is unable to determine which "SmithT" you want, Tom or Tina.

Edited by - MuffinMan on 04/22/2002 09:56:32

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-22 : 09:54:52

I think the crux of the biscuit is the process.

You want to do a select on that surname, if it returns more than one row, then you want to append the first letter. Maybe something like

Create Procedure AuthenticateUser

@LastName varchar(100)

AS

Set Nocount On

Declare @UserCount int

Select @UserCount = Count(*) FROM Users Where Username = @Lastname

If @UserCount = 1

Select UserID FROM Users Where Username = lastname

Else

Select UserID FROM Users Where Username = lastname + left(firstname,1)

Set Nocount off

How's that for ya ?

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-22 : 09:56:37

Sniped by a Muffin!

Damian

Nazim
A custom title

1408 Posts

Posted - 2002-04-22 : 10:29:02

CONCENTRATE on your job MERKIN!!!!!!!!!!!

you dont get titles for free

--------------------------------------------------------------

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-22 : 21:23:54

quote:

CONCENTRATE on your job MERKIN!!!!!!!!!!!

you dont get titles for free

No, you have to post miscellaneous comments on other people's posts to really climb the ladder fast around here...

tribune
Posting Yak Master

105 Posts

Posted - 2002-04-22 : 21:43:18

quote:

quote:

CONCENTRATE on your job MERKIN!!!!!!!!!!!

you dont get titles for free

No, you have to post miscellaneous comments on other people's posts to really climb the ladder fast around here...

free toast :)

Nazim
A custom title

1408 Posts

Posted - 2002-04-23 : 00:35:47

Nah! Now that Am a General .Am not too worried about my post count and btw isnt General's primary duty is discipline