Generate Password Procedure
Stored procedure 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. It can generate from 1 to 10,000 passwords as a result set.
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. A single number or special character separates syllables, except in the case of 2 syllables. If there are only 2 syllables, they will be separated by a number and a special character.
Input parameters @SYLLABLE_COUNT and @PASSWORD_COUNT determine the password length and the number of passwords.
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_COUNTint = null ,
@PASSWORD_COUNTint= null ,
@PASSWORD_STRENGTHfloat= nulloutput
)
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 (
numberintnot 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 intnot null
identity(1,1) primary key clustered,
[PASSWORD]varchar(32)not null
)
insert into @password ([PASSWORD])
selecttop 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_COUNTint
declare @PASSWORD_COUNTint
declare @PASSWORD_STRENGTHfloat
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 ''
Results of Test Script:
@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@Doz8ZowFic>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}VawLet@Yoq9Sav
5 Hav@Qer/Met7Zig&Jiw4Pot-Fod(Zat
6 Bid_Lal+Bay3Fos9FezFaw!Kad4Zok
7 Qar-Kig-Lem3Yeq?Xuj7Zun,Xid=Xel
8 Biq6Jot:Caj(Xun2Kup[Fax|Gec,Xon
9 Yac7Nox^Woy~Wag0XanHil3Cab/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$TewFun7Meq2Kik
19 Caq1Qag{Pes{Gex|Til=Vuk7Tig1Vur
20 Miw)Law}Tun2Lop.Jix#Riq|Yat$Juc
@PASSWORD_STRENGTH = 1.46214e+037
CODO ERGO SUM
View Replies !
TRACKER
