Making a mountain out of a molehill

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

Transact-SQL (2000)

Making a mountain out of a molehill

Author

Topic

GS1
Starting Member

27 Posts

Posted - 2003-05-09 : 08:23:05

Hi

I need to make a table that contains a user_id and a password so that people can log into a restricted area of a website. This is obviously simple enough to set up; the catch is that I need to generate a random 4 digit number as the password for each user.

hmmmm - I think this may be a job for a cursor, but how?

I've set up a cursor as follows to build a temp table and stick a random number in it

************************************************************************************

create table #rand (random varchar(4))

go

declare @int int
declare @rand varchar(4)

declare c1 cursor
for
select 1

open c1
fetch next from c1 into @int

while @int<=300

begin

set @rand = (select cast(cast(rand()*10 as int)as varchar(1))
+ cast(cast(rand()*10 as int)as varchar(1))
+ cast(cast(rand()*10 as int)as varchar(1))
+ cast(cast(rand()*10 as int)as varchar(1)))

insert #rand (random)
select @rand

set @int = @int + 1

fetch next from c1 into @int

end

close c1
deallocate c1

*******************************************************************************

But what to do next? I am really stumped. I had tried to put the user names into a table first, and then use a cursor to put a random number in each record, but for some reason the random numbers generated were always 1111,2222,4444 etc.

Maybe I'm going at this completely the wrong way - please could somebody give me a hand?

Thanks

G

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-09 : 08:37:33

Here's an idea using a fuction that's great when it is needed: NEWID (look it up in BOL). I use NEWID to randomize record selection but that's another story. Your requirement sounds like a weak 4 char password will suffice. This solution provides only numeric values...

Create your table
Fill it with Usernames, then

UPDATE MyTable
SET MyPassword = LEFT(NEWID(),4)

That ought to do it.

If you want stronger passwords (alphanumeric, special character, 8 character) this solution won't do.

Sam

GS1
Starting Member

27 Posts

Posted - 2003-05-09 : 09:23:37

Thanks, but this doesn't seem to work as NewID() does not create a random number - it creates a varchar that is the same each time.

G

GS1
Starting Member

27 Posts

Posted - 2003-05-09 : 10:08:37

Ive cracked it now - I modified the temp table to hold an incrementing number as well as the random numbers generated by the cursor, then used a second cursor (nightmare) to select the random numbers into the destination table...

I would post the sql but I expect that it is too related to my db to be much use to anyone...

I'd still love to know a more straightforward way of doing this...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-09 : 10:23:33

I think what Sam meant was ...


UPDATE MyTable 
SET MyPassword = right(convert(varchar,rand(convert(binary(4),newid()))),4)

Jay White
{0}

macka
Posting Yak Master

162 Posts

Posted - 2003-05-09 : 10:31:47

Rather than inserting the users and then assigning the passwords, why not assign the password using a DEFAULT CONSTRAINT ? That way, the database takes care of issuing passwords.

(using Sam's/Jay's code)


ALTER TABLE MyUsers 
ADD CONSTRAINT DF_MyUsers_Password 
DEFAULT right(convert(varchar,rand(convert(binary(4),newid()))),4) FOR pwd

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-09 : 10:32:35

quote:

I think what Sam meant was ...

doubt it, unless you wanted dots, hyphens and the odd 3 character string too

Edited by - Arnold Fribble on 05/09/2003 10:34:40

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-09 : 10:44:14

AF, how do you figure?

Jay White
{0}

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-09 : 10:49:53

Like this:


SELECT RIGHT(r,4) AS p, r
FROM (
    SELECT CONVERT(varchar, RAND(CONVERT(binary(4), NEWID()))) AS r
    FROM Numbers
    GROUP BY n
  ) AS A
WHERE RIGHT(r,4) NOT LIKE '[0-9][0-9][0-9][0-9]'


p    r                              
---- ------------------------------ 
.584 0.584
.576 0.576
.612 0.612
.083 0.083
.752 0.752
.897 0.897
.977 0.977
.607 0.607
-005 9.86657e-005
.885 0.885
.887 0.887
.926 0.926
.493 0.493
.645 0.645
0.08 0.08
.306 0.306
.684 0.684
.131 0.131
.631 0.631
etc.

Maybe this would be better:
right(str(rand(convert(binary(4),newid()))),6,4),4)

Edited by - Arnold Fribble on 05/09/2003 11:01:02

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-09 : 11:27:13

never ever doubt the fribble ...

good catch.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources