Gaby: The repeating characters, either caps or lower case aren't a big deal. The first time I tested the proc, it just seemed to have an inordinate number of paired repeats. I went back this morning and tested again, and now there are much fewer "paired" characters. I also tried just the alpha setting and I personally like that result much better as a 'password'. Character representations are easier for me to remember, instead of having the mixed special characters in the phrase as well.
No big deal, but I was just curious if perhaps having the routine NOT use a character that has already been used in the password would make the resulting phrase a "better" password or not. I realize the code to do this would be really intense and may not be worth the effort. I guess it all depends on how you look at password construction - with or without repeating characters.
Thanks for the proc. I will use it in the future. JT Nelson
Here's a modified form of the script. It takes an argument @uniquechars. If set to 1, gradually shrinks the @string variable by removing the chosen character. The exception is if your @string is less than your @passlen, it won't work (so for large passwords, you have no choice but to use repeating characters).
-- This generates a random password, defaulting to 10 characters
create procedure msdb.dbo.GetPass
@passlen int = 10, @uniquechars int = 0, @charset int = 0 -- 2 is alphanumeric + special characters,
-- 1 is alphanumeric, 0 is alphabetical only
set nocount on
if (@passlen > 8000 or @passlen < 1) -- Let's not go crazy here
select @passlen = 10
declare @password varchar(8000), @string varchar(256), @numbers varchar(10), @extra varchar(50),
@stringlen int, @index int, @passval varchar(1)
-- no 1, l, I, 0, O which can cause confusion
select @string = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz' -- same as @charset = 0
select @numbers = '23456789'
select @extra = '>_!@#$%&=?<' -- add more special characters if you want
if @charset = 2
select @string = @string + @numbers + @extra
else if @charset = 1
select @string = @string + @numbers
-- else assume @extra is 0 and @string is just letters. Feel free to modify these criteria as you see fit
select @stringlen = len(@string)
select @password = ''
-- This check is if your password length exceeds the number of unique characters. If so, the @uniquechars setting
-- you turned on (1) is turned back off.
if ((@stringlen < @passlen) and (@uniquechars = 1))
select @uniquechars = 0
while (@passlen > 0)
-- For the random part here, use rand() or, preferably, newid()
select @index = (abs(checksum(newid())) % @stringlen) + 1
select @passval = substring(@string, @index, 1)
if @uniquechars = 1 -- modified section that removes repeating letters
select @string = replace(@string, @passval, '')
select @password = @password + @passval
select @passlen = @passlen - 1
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein