if objectproperty(object_id('dbo.P_GENERATE_PASSWORDS'),'IsProcedure') = 1
	begin drop procedure dbo.P_GENERATE_PASSWORDS end
go
create procedure dbo.P_GENERATE_PASSWORDS
	(
	@SYLLABLE_COUNT		int 	= null ,
	@PASSWORD_COUNT		int	= null ,
	@PASSWORD_STRENGTH	float	= null	output
	)
as

/*
Procedure Name: P_GENERATE_PASSWORDS


Procedure Description:

P_GENERATE_PASSWORDS returns a list of randomly generated passwords
designed to meet typical password complexity requirements of a minimum
of 8 characters, with at least one each of uppercase letters,
lowercase letters, numbers, and special characters.

The passwords are meant to be somewhat mnemonic by generating
syllables consisting of an uppercase consonant, followed by a
lower case vowel, and a lowercase consonant.  Syllables are separated
by a single number or special character, except in the case of 2 syllables.
If there are only 2 syllables, the syllables will be separated by
a number and a special character.

Passwords can be from 2 to 8 syllables in length.

Input parameter @SYLLABLE_COUNT is the total syllables in each output password.
The value of @SYLLABLE_COUNT must be between 2 and 8.  If it is < 2 or null,
it is set to 3.  If it is > 8 it is set to 8.

Input parameter @PASSWORD_COUNT is the total passwords to be returned.
The value of @SYLLABLE_COUNT must be between 1 and 10,000.
If it is < 1, it is set to 1. If it is null, it is set to 10.
If it is > 10,000 it is set to 10,000.

Output parameter @PASSWORD_STRENGTH returns the total possible
passwords that are possible for the selected @SYLLABLE_COUNT.

*/

set nocount on


-- Set password syllable count
set @SYLLABLE_COUNT =
	case
	when @SYLLABLE_COUNT is null
	then 3
	when @SYLLABLE_COUNT < 2
	then 3
	when @SYLLABLE_COUNT > 8
	then 8
	else @SYLLABLE_COUNT
	end

-- Set password count
set @PASSWORD_COUNT =
	case
	when @PASSWORD_COUNT is null
	then 10
	when @PASSWORD_COUNT < 1
	then 1
	when @PASSWORD_COUNT > 10000
	then 10000
	else @PASSWORD_COUNT
	end

declare @con varchar(200)
declare @vowel varchar(200)
declare @special varchar(200)
declare @num varchar(200)
declare @special_only varchar(200)
declare @con_len int
declare @vowel_len int
declare @special_len int
declare @num_len int
declare @special_only_len int
declare @strings int

-- set character strings for password generation
select
	@con		= 'bcdfghjklmnpqrstvwxyz',
	@vowel		= 'aeiou',
	@num		= '1234567890',
	@special_only	= '~!@#$%^&*()_+-={}|[]\:;<>?,./'

set 	@special = @num+@special_only

-- set string lengths
select	@con_len		= len(@con),
	@vowel_len		= len(@vowel),
	@special_len		= len(@special),
	@num_len		= len(@num),
	@special_only_len	= len(@special_only) ,
	@strings 		=
			case
			when @SYLLABLE_COUNT < 3
			then 2
			else @SYLLABLE_COUNT-1
			end

--select @con, @vowel, @special, @num, @special_only,
--SELECT @con_len, @vowel_len, @special_len, @num, @special_only_len, @strings

-- Declare number tables to generate rows
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

declare @rows_needed_root int
set @rows_needed_root = convert(int,ceiling(sqrt(@PASSWORD_COUNT)))

-- Load number 0 to 16
insert into @num1 (NUMBER)
select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9
order by 1

-- Load table with numbers zero thru square root of the number of rows needed +1
insert into @num2 (NUMBER)
select
	NUMBER = a.NUMBER+(10*b.NUMBER)
from
	@num1 a cross join @num1 b
where
	a.NUMBER+(10*b.NUMBER) < 
	@rows_needed_root
order by
	1

-- Load table with the number of passwords needed
insert into @num3 (NUMBER)
select
	NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
	@num2 a
	cross join
	@num2 b
where
	a.NUMBER+(@rows_needed_root*b.NUMBER) < @PASSWORD_COUNT
order by
	1

-- Declare password string table
declare @p table (
	number	int		not null
		primary key clustered,
	m1 	varchar(10)	not null,
	m2 	varchar(10)	not null,
	m3 	varchar(10)	not null,
	m4 	varchar(10)	not null,
	m5 	varchar(10)	not null,
	m6 	varchar(10)	not null,
	m7 	varchar(10)	not null,
	m8 	varchar(10)	not null,
	
	s1 	varchar(10)	not null,
	s2 	varchar(10)	not null,
	s3 	varchar(10)	not null,
	s4 	varchar(10)	not null,
	s5 	varchar(10)	not null,
	s6 	varchar(10)	not null,
	s7 	varchar(10)	not null
)

insert into @p
select
	NUMBER,
	-- M1 through M8 will be syllables composed of a single randomly selected
	-- uppercase consonant, a single randomly selected lowercase vowel,
	-- followed by as single randomly selected lowercase consonant.
	m1 =
		upper(substring(@con,  (R11%@con_len)+1,1))+
		substring(@vowel,(R12%@vowel_len)+1,1)+
		substring(@con,  (R13%@con_len)+1,1),
	m2 =
		upper(substring(@con,  (R21%@con_len)+1,1))+
		substring(@vowel,(R22%@vowel_len)+1,1)+
		substring(@con,  (R23%@con_len)+1,1),
	m3 =
		upper(substring(@con,  (R31%@con_len)+1,1))+
		substring(@vowel,(R32%@vowel_len)+1,1)+
		substring(@con,  (R33%@con_len)+1,1),
	m4 =
		upper(substring(@con,  (R41%@con_len)+1,1))+
		substring(@vowel,(R42%@vowel_len)+1,1)+
		substring(@con,  (R43%@con_len)+1,1),
	m5 =
		upper(substring(@con,  (R51%@con_len)+1,1))+
		substring(@vowel,(R52%@vowel_len)+1,1)+
		substring(@con,  (R53%@con_len)+1,1),
	m6 =
		upper(substring(@con,  (R61%@con_len)+1,1))+
		substring(@vowel,(R62%@vowel_len)+1,1)+
		substring(@con,  (R63%@con_len)+1,1),
	m7 =
		upper(substring(@con,  (R71%@con_len)+1,1))+
		substring(@vowel,(R72%@vowel_len)+1,1)+
		substring(@con,  (R73%@con_len)+1,1),
	m8 =
		upper(substring(@con,  (R81%@con_len)+1,1))+
		substring(@vowel,(R82%@vowel_len)+1,1)+
		substring(@con,  (R83%@con_len)+1,1),

	-- S1 through S7 will each be a single randomly selected
	-- number or special character.  At least one of the used
	-- columns will be a number and one will be a special character.

	s1 =
		case
		when NUMBER_COL = 1
		then substring(@num,(RS1%@num_len)+1,1)
		when SPECIAL_COL = 1
		then substring(@special_only,(RS1%@special_only_len)+1,1)
		else substring(@special,(RS1%@special_len)+1,1)
		end,
	s2 =
		case
		when NUMBER_COL = 2
		then substring(@num,(RS2%@num_len)+1,1)
		when SPECIAL_COL = 2
		then substring(@special_only,(RS2%@special_only_len)+1,1)
		else substring(@special,(RS2%@special_len)+1,1)
		end,
	s3 =
		case
		when NUMBER_COL = 3
		then substring(@num,(RS3%@num_len)+1,1)
		when SPECIAL_COL = 3
		then substring(@special_only,(RS3%@special_only_len)+1,1)
		else substring(@special,(RS3%@special_len)+1,1)
		end,
	s4 =
		case
		when NUMBER_COL = 4
		then substring(@num,(RS4%@num_len)+1,1)
		when SPECIAL_COL = 4
		then substring(@special_only,(RS4%@special_only_len)+1,1)
		else substring(@special,(RS4%@special_len)+1,1)
		end,
	s5 =
		case
		when NUMBER_COL = 5
		then substring(@num,(RS5%@num_len)+1,1)
		when SPECIAL_COL = 5
		then substring(@special_only,(RS5%@special_only_len)+1,1)
		else substring(@special,(RS5%@special_len)+1,1)
		end,
	s6 =
		case
		when NUMBER_COL = 6
		then substring(@num,(RS6%@num_len)+1,1)
		when SPECIAL_COL = 6
		then substring(@special_only,(RS6%@special_only_len)+1,1)
		else substring(@special,(RS6%@special_len)+1,1)
		end,
	s7 =
		case
		when NUMBER_COL = 7
		then substring(@num,(RS7%@num_len)+1,1)
		when SPECIAL_COL = 7
		then substring(@special_only,(RS7%@special_only_len)+1,1)
		else substring(@special,(RS7%@special_len)+1,1)
		end
from
(
select
	aaaa.*,
	-- Select random columns numbers to force at least
	-- one special character and one number character
	-- in each password
	NUMBER_COL  = (X1%@strings)+1 ,
	SPECIAL_COL = ((((X2%(@strings-1))+1)+X1)%@strings)+1
from
(
select top 100 percent
	NUMBER,
	-- Generate random numbers for password generation
	R11 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R12 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R13 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R21 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R22 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R23 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R31 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R32 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R33 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R41 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R42 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R43 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R51 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R52 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R53 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R61 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R62 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R63 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R71 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R72 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R73 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R81 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R82 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R83 = abs(convert(bigint,convert(varbinary(20),newid()))),

	RS1 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS2 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS3 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS4 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS5 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS6 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS7 = abs(convert(bigint,convert(varbinary(20),newid()))),

	X1 = convert(bigint,abs(convert(int,convert(varbinary(20),newid())))),
	X2 = convert(bigint,abs(convert(int,convert(varbinary(20),newid()))))
from
	@num3 aaaaa
	
order by
	aaaaa.NUMBER
) aaaa ) aaa
order by
	aaa.NUMBER

-- Compute password strength as the total possible passwords
-- for the selected number of syllables.
select
	@PASSWORD_STRENGTH =
	power((@con_len*@con_len*@vowel_len)*1E,@SYLLABLE_COUNT*1E)*
	(@special_only_len*@num_len*1E)*
	case
	when @strings < 3
	then 1E
	else power(@special_len*1E,(@strings-2)*1E)
	end

-- Declare output table
declare @PASSWORD table 
(
	NUMBER 		int		not null
			identity(1,1) primary key clustered,
	[PASSWORD]	varchar(32)	not null 
)

insert into @password ([PASSWORD])
select	top 100 percent
	[PASSWORD]
from
	(
	select
		distinct
		[PASSWORD] =
		convert(varchar(32),
		case
		when @SYLLABLE_COUNT = 2
		then m1+s1+s2+m2
		else
		substring(m1+s1+m2+s2+m3+s3+m4+s4+m5+s5+m6+s6+m7+s7+m8
		,1,(@SYLLABLE_COUNT*4)-1)
		end)
	from @P
	) a
where
	-- Verify at least one number in password
	[PASSWORD] like '%[1234567890]%'		and
	-- Verify at least one special character in password
	[PASSWORD] like '%[^a-z1234567890]%'
order by
	newid()

select * from @password order by NUMBER

return 0
go
grant execute on dbo.P_GENERATE_PASSWORDS to public

go

-- Test Script
declare @SYLLABLE_COUNT		int
declare @PASSWORD_COUNT		int
declare @PASSWORD_STRENGTH	float

select @SYLLABLE_COUNT = 2 , @PASSWORD_COUNT = 5
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 3 , @PASSWORD_COUNT = 6
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 5 , @PASSWORD_COUNT = 7
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 8 , @PASSWORD_COUNT = 20
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''

@SYLLABLE_COUNT = 2, @PASSWORD_COUNT = 5
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Tis|2Fun
2           Miy5]Fib
3           Bay1|Puz
4           Tel3.Pus
5           Duq0@Roy

@PASSWORD_STRENGTH	 = 1.40999e+009
 
@SYLLABLE_COUNT = 3, @PASSWORD_COUNT = 6
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Qab@Kaz0Lan
2           Sav1Tig]Hat
3           Pah6Fic|Cic
4           Buz7Viz=Mec
5           Vig^Wah9Xuf
6           Qew2Mif^Mix

@PASSWORD_STRENGTH	 = 3.10902e+012
 
@SYLLABLE_COUNT = 5, @PASSWORD_COUNT = 7
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Mux4Zor_Jog{Vec,Bih
2           Ker1Qem[Gat,Hut|Zif
3           Red}Ciq5Ber%Son:Qej
4           Cov@Doz8Zow\Fic>Pos
5           Tad0Bek&Fug_Kiv9Rez
6           Pil1Nul$Vil~Koh_Xel
7           Zuk4Gir&Yep|Ned)Sap

@PASSWORD_STRENGTH	 = 2.29917e+022
 
@SYLLABLE_COUNT = 8, @PASSWORD_COUNT = 20
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Biz&Xak9Gew{Vuf[Tix;Qap-Bik{Vay
2           Rof<Job*Fax-Niq/Zew9Pah:Bag(Zok
3           Noh1Nor7Rul5Fon@Mig>Xod.Lay.Maq
4           Piw:Keb}Rod8Yah}Vaw\Let@Yoq9Sav
5           Hav@Qer/Met7Zig&Jiw4Pot-Fod(Zat
6           Bid_Lal+Bay3Fos9Fez\Faw!Kad4Zok
7           Qar-Kig-Lem3Yeq?Xuj7Zun,Xid=Xel
8           Biq6Jot:Caj(Xun2Kup[Fax|Gec,Xon
9           Yac7Nox^Woy~Wag0Xan\Hil3Cab/Nit
10          Pod+Kor%Fov7Vil,Dor:Xoq!Kel3Poq
11          Goc)Roz7Ruq/Pad8Jeh*Xaj&Dew{Duy
12          Sik/Ruj@Wiv9Qik[Sub=Qim,Ned:Qit
13          Les9Har&Ceb5Heg^Fov0Vaf1Fuf[Maq
14          Deg6Yiw$Peg:Wuj7Woc_Mip|Kam9Zus
15          Nix^Dev%Qoj=Seq[Jig6Lig}Day-Ric
16          Dux;Woy=Zud1Mak5Yej$Kav2Mek5Buh
17          Yuv8Mor9Wix&Giq5Zar@Nuk$Pey<Lok
18          Dem~Kof-Yoq(Xig$Tew\Fun7Meq2Kik
19          Caq1Qag{Pes{Gex|Til=Vuk7Tig1Vur
20          Miw)Law}Tun2Lop.Jix#Riq|Yat$Juc

@PASSWORD_STRENGTH	 = 1.46214e+037

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[RandomPassword]
(
@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
DECLARE @password varchar(25), @type tinyint, @bitmap char(5)
SET @password='' 
SET @bitmap = 'uaeio' 
-- @bitmap contains all the vowels, which are a, e, i, o and u. 
-- 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
		begin
			-- (RAND() * (4)) -> 4 = len(@bitmap) - 1, it's hardcoded for speed
			SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (4)),0)),1)
		end
		ELSE --Appending a random alphabet
		begin
			-- we eliminte the possibility that char from bitmap and the random one are same
			declare @char char(1)
			set @char = CHAR(ROUND(97 + (RAND() * (25)),0))
			while @bitmap like '%' + @char + '%'
			begin
				set @char = CHAR(ROUND(97 + (RAND() * (25)),0))
			end
			SET @password = @password + @char
		end
	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

-- here we add one number to the end of string if the password is simple type.
-- we could add it somewhere else with little recoding
IF @password_type = 'simple'
	select @password = LEFT ( @password, len(@password) - 1) + CHAR ( ROUND(48 + (RAND() * (9)),0))

SELECT @password as NewPassword

quote:

---

Originally posted by spirit1

this a bit modified vyas's sproc i use.
I had to generate 5000 simple readable passwords with this and it was up to the chanllenge ...

---

Simple password
--------------- 
amijebe2
owavega3
iwehiku2
imegebi6
ozaxafa9
alicema1
aqagapo4
inamuca6
esadeza4
ekuniga7

Strong password
--------------- 
B4zI1-5U
hW4K6KM'
3pNkfp,G
I1Q#*E16
3Di5h0a-
4*4T1jH4
3*T8bM9R
+71Snm#5
'8bH5(WG
9WN87N1z

quote:

---

Originally posted by harsh_athalye

The SP you created is great, MVJ, but what I was saying that instead keeping keeping bitmap of characters, numbers and special characters, we can have a dictionary of uncommon words and may be mix those words to create simple-to-remember but difficult to guess passwords. (along with random uppercase letters and numbers for additional complexity)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

quote:

---

Originally posted by Peso

CREATE PROCEDURE dbo.uspCreatePassword...]

---

Password
---------
u#n4PesP
vYqI0tc(
XEhm1x_y
#zaqyX4P
FsAx8qn!
NnTy6_op
x7+JXycs
+UZz1hfz
(SSk4agv
My$0gdwH