For those of you who want to generate a random password here is a procedure you can use. The code is a little excessive but it has configurable parameters for length, numbers, caps/lower, etc.
Enjoy!
----------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[generate_password]
@length int = 10 -- the desired length of the password int <= 30
, @numbers varchar(1) = 'Y' -- use numbers
, @specials varchar(1) = 'Y' -- use special characters
, @caps varchar(1) = 'Y' -- use caps and lower case
, @includeconfusingletters varchar(1) = 'N' -- include letters that resemble numbers when typed out
as
--****************************************************************************************************************************************
-- Procedure: [dbo].[generate_password]
-- Description: This procedure is used to generate a random password
--****************************************************************************************************************************************
declare @int int, @letter int, @cap int, @letter_or_number int, @number int, @special int;
declare @cap_used varchar(1), @lower_used varchar(1), @number_used varchar(1), @special_used varchar(1);
declare @var varchar(30)
select @lower_used = 'N', @var = '', @int = 1; --@length = 10,
if upper(@numbers) not in ('Y', 'N') set @numbers = 'Y';
if upper(@specials) not in ('Y', 'N') set @specials = 'Y';
if upper(@caps) not in ('Y', 'N') set @caps = 'Y';
set @numbers = upper(@numbers);
set @specials = upper(@specials);
set @caps = upper(@caps);
if @numbers = 'N' set @number_used = 'Y' else set @number_used = 'N';
if @specials = 'N' set @special_used = 'Y' else set @special_used = 'N';
if @caps = 'N' set @cap_used = 'Y' else set @cap_used = 'N';
if @numbers = 'N' set @includeconfusingletters = 'Y';
if @length < 4
begin
print 'Minimum password length is 4 characters... setting password length to 4 characters';
set @length = 4
end
if @length > 30
begin
print 'Maximum password length is 30 characters... setting password length to 30 characters';
set @length = 30
end
while @int <= (@length) or (@cap_used = 'N' or @lower_used = 'N' or @number_used = 'N' or @special_used = 'N')
begin
select @letter = ceiling(rand() * 26)
select @cap = round((rand() * 1),0)
select @letter_or_number = floor(rand() * 3)
select @number = floor(rand() * 10)
select @special = ceiling(rand() * 10)
if @numbers = 'Y' and @specials = 'Y'
begin
select @letter_or_number = floor(rand() * 3)
end
else
begin
if @numbers = 'Y' and @specials = 'N'
begin
select @letter_or_number = floor(rand() * 2)
end
else
begin
if @numbers = 'N' and @specials = 'Y'
begin
while @letter_or_number is null or @letter_or_number = 1
begin
select @letter_or_number = floor(rand() * 3)
end
end
else
begin
if @numbers = 'N' and @specials = 'N'
begin
select @letter_or_number = 0
end
end
end
end
if @caps = 'N' set @cap = 0;
if @int = @length - 3 and @lower_used = 'N'
begin
select @letter_or_number = 0, @cap = 0;
end
if @int = @length - 2 and @cap_used = 'N'
begin
select @letter_or_number = 0, @cap = 1;
end
if @int = @length - 1 and @number_used = 'N'
begin
select @letter_or_number = 1;
end
if @int = @length and @special_used = 'N'
begin
select @letter_or_number = 2;
end
if @int = 1
begin
select @letter_or_number = 0;
end
set @var = @var + case when @letter_or_number = 0 then
case when @cap = 0 then
case when @letter = 1 then 'a'
when @letter = 2 then 'b'
when @letter = 3 then 'c'
when @letter = 4 then 'd'
when @letter = 5 then 'e'
when @letter = 6 then 'f'
when @letter = 7 then 'g'
when @letter = 8 then 'h'
when @letter = 9 then 'i'
when @letter = 10 then 'j'
when @letter = 11 then 'k'
when @letter = 12 then case when @includeconfusingletters = 'N' then 'k' else 'l' end
when @letter = 13 then 'm'
when @letter = 14 then 'n'
when @letter = 15 then 'o'
when @letter = 16 then 'p'
when @letter = 17 then 'q'
when @letter = 18 then 'r'
when @letter = 19 then 's'
when @letter = 20 then 't'
when @letter = 21 then 'u'
when @letter = 22 then 'v'
when @letter = 23 then 'w'
when @letter = 24 then 'x'
when @letter = 25 then 'y'
when @letter = 26 then 'z'
end
else
upper(case when @letter = 1 then 'a'
when @letter = 2 then 'b'
when @letter = 3 then 'c'
when @letter = 4 then 'd'
when @letter = 5 then 'e'
when @letter = 6 then 'f'
when @letter = 7 then 'g'
when @letter = 8 then 'h'
when @letter = 9 then 'i'
when @letter = 10 then 'j'
when @letter = 11 then 'k'
when @letter = 12 then 'l'
when @letter = 13 then 'm'
when @letter = 14 then 'n'
when @letter = 15 then case when @includeconfusingletters = 'N' then 'n' else 'o' end
when @letter = 16 then 'p'
when @letter = 17 then 'q'
when @letter = 18 then 'r'
when @letter = 19 then 's'
when @letter = 20 then 't'
when @letter = 21 then 'u'
when @letter = 22 then 'v'
when @letter = 23 then 'w'
when @letter = 24 then 'x'
when @letter = 25 then 'y'
when @letter = 26 then 'z'
end)
end
when @letter_or_number = 1 then rtrim(convert(varchar(2), @number))
else
case ceiling(rand() * 14)
when 1 then '!'
when 2 then '@'
when 3 then '#'
when 4 then '$'
when 5 then '%'
when 6 then '^'
when 7 then '&'
when 8 then '*'
when 9 then '('
when 10 then ')'
when 11 then '+'
when 12 then '<'
when 13 then '>'
when 14 then '~'
else '!'
end
end
if @letter_or_number = 0 and @cap = 0
begin
set @lower_used = 'Y';
end
if @letter_or_number = 0 and @cap <> 0
begin
set @cap_used = 'Y';
end
if @letter_or_number = 1
begin
set @number_used = 'Y';
end
if @letter_or_number = 2
begin
set @special_used = 'Y';
end
set @int = @int + 1;
end
select @var as [password];
---------------------------------------------------------------------
The EckMan 