[thelist] SQL 2000 auto create ID's

Ashok at MagicalKenya.com Ashok at MagicalKenya.com
Wed Aug 14 11:32:01 CDT 2002

>I want SQL 2000 to automatically create unique ID's
>and passwords for users to have access to a website.
>Each unique ID must start with 'AB'.
>Then I want to add after the 'AB' seven random
>alpha and/or numeric characters.
>The password can be anything, six characters.

You can do this very easily using SQL Server , try the following  TSQL in a
sqlserver stored procedure :

declare @rndpass varchar(10) -- random password
declare @rnduser varchar(255) -- random user id

select @rndpass = CAST( Rand() as varchar(10))
select @rndpass = substring(@rndpass,3,7)

select @rnduser = CONVERT(varchar(255), NEWID())
select @rnduser = 'AB'+substring(@rnduser,1,7)

--rnduser & rndpass have the generated username & password
select @rnduser, @rndpass

--- insert into table .....code.....


the password is a six digit number generated using the RAND() function.
the user name is a random alpha-numeric combination (NEWID() returns a GUID
prefixed with an 'AB'


Ashok Hariharan

More information about the thelist mailing list