Stored procedure to generate a simple or complex random password

his procedure generates random passwords using RAND() function. It can be configured to generate a simple or a complex password. You can also customize the length of the password generated. Complex passwords will include upper and lower case letters, numbers and special characters. See the code to realize how useful the RAND() function is! When you choose to generate a simple password (default behavior), SPECIAL CARE is taken to generate meaningful/easy to remember passwords.

CREATE PROC random_password
(
@len int = 8, --Length of the password to be generated
@password_type char(7) = 'simple'
--Default is to generate a simple password with lowecase letters.
--Pass anything other than 'simple' to generate a complex password.
--The complex password includes numbers, special characters, upper case and lower case letters
)
AS
/****************************************************
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: To generate a random password
Written by: Narayana Vyas Kondreddi http://vyaskn.tripod.com
Tested on: SQL Server 7.0 and SQL Server 2000
Date modified: March-29-2001 01:15 PM
Email: vyaskn@hotmail.com

Examples:
To generate a simple password with a length of 8 characters:
EXEC random_password
To generate a simple password with 6 characters:
EXEC random_password 6
To generate a complex password with 8 characters:
EXEC random_password @Password_type = 'complex'
To generate a comples password with 6 characters:
EXEC random_password 6, 'complex'
*************************************************/
BEGIN
DECLARE @password varchar(25), @type tinyint, @bitmap char(6)
SET @password=''
SET @bitmap = 'uaeioy'
--@bitmap contains all the vowels, which are a, e, i, o, u and y. These vowels are used to generate slightly readable/rememberable simple passwords

WHILE @len > 0
BEGIN
IF @password_type = 'simple' --Generating a simple password
BEGIN
IF (@len%2) = 0 --Appending a random vowel to @password

SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (5)),0)),1)
ELSE --Appending a random alphabet
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))

END
ELSE --Generating a complex password
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)

IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
END

SET @len = @len - 1
END

SELECT @password --Here's the result

END