I h
Let us see the stored procedure here:
Create PROCEDURE GenerateRandomPwd2 (@length int = 20, @allowAtoZ BIT = 1, @allow0to9 BIT = 1, @allowSpecials1 BIT = 1, @allowSpecials2 BIT = 1, @avoidAmbiguousCharacters BIT = 1) AS BEGIN DECLARE @range Varchar(90) = '' -- Start by assuming @avoidAmbiguousCharacters is true if @allowAtoZ = 1 begin set @range = @range +'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz' end if @allow0to9 = 1 begin set @range = @range +'23456789' end if @allowSpecials1 = 1 begin set @range = @range +'!"#$%&''()*+,-./' end if @allowSpecials2 = 1 begin set @range = @range +':;<=>?@' end if @avoidAmbiguousCharacters = 0 begin -- put back capital i, lowercase l, capital o, the number(s) 0, 1 if @allowAtoZ = 1 begin set @range = @range +'IOl' end if @allow0to9 = 1 begin set @range = @range +'01' end end DECLARE @pwd VARCHAR(512) = '' --use master.dbo.spt_values as a pseudo tally(numbers) table SELECT TOP (@length) @pwd += SUBSTRING(@range,fn.rnd,1) FROM master.dbo.spt_values sv CROSS APPLY ( SELECT [rnd] = (ABS(CHECKSUM(NEWID())) % (LEN(@range))) ) fn SELECT [Password] = @pwd, [@allowAtoZ] = @allowAtoZ, [@allow0to9] = @allow0to9, [@allowSpecials1] = @allowSpecials1, [@allowSpecials2] = @allowSpecials2, [@avoidAmbiguousCharacters] = @avoidAmbiguousCharacters END GO
You can execute above stored procedure by running the following code:
EXEC GenerateRandomPwd2 @length = 20, @avoidAmbiguousCharacters = 0 EXEC GenerateRandomPwd2 @length = 20, @allow0to9 = 0, @allowSpecials1 = 0, @allowSpecials2 = 0, @avoidAmbiguousCharacters = 0 EXEC GenerateRandomPwd2 @length = 20, @allow0to9 = 0, @allowSpecials1 = 0, @allowSpecials2 = 0, @avoidAmbiguousCharacters = 1 EXEC GenerateRandomPwd2 @length = 20, @allowAtoZ = 0, @allow0to9 = 0, @allowSpecials1 = 1, @allowSpecials2 = 1
Please note that I am not saying that earlier version is better or this version is better. Both of them works great and generates the same random password. I will let you use any version which you want to use it. In a business environment, I have found this SP very helpful as nowadays, it is very difficult to depend on the users to come up with a complex password. They often get it wrong. During the registration process on the page, I usually display automatically the random password. If they do not like the password, they can just click on another button and generate another password. It always helps users to give guidance on what will work as a good password.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
10 Comments. Leave new
That’s an interesting change. I like it also. He used the tally to substring out the random chars. :)
I am glad to hear that.
Seems nobody knows and uses very strong PRNG built into SQL Server – CRYPT_GEN_RANDOM() that generates far better randomness than this NEWID() hacks.
Hi Pinal,
Thank you for another interesting article. I wrote a similar function for SQL 2008 using the CRYPT_GEN_RANDOM function to generate a varbinary of the required length. The byte sequence was then be used to index into the allowed character list similar to your solution. It’s another idea on how this can be done.
Interesting. Can you please provide link?
Hi Pinal, sorry, it was a client project and I don’t have the scripts. I have emailed you a new script. Thanks.
Nice code, I like the way the tally table is used.
A barely related question: Should one attempt to filter random passwords like this for obscenities? If so, how and how much? I accept that it’s impossible to avoid offending everyone…
Great thought but I know many who have such password :)
It’s fine if a user wants to use an obscene password. Maybe not so much if a user clicks a button to create a random password and gets an obscenity back…
SELECT NEWID()