Earlier last month, I posted an interview question – How to Generate Random Passwords in SQL Server? – Interview Question of the Week #181. Though, my efforts were honest my answer was not complete in that blog post. Thankfully, SQL Server Expert Tim Cartwright noticed the error in the blog post and decided to send me to follow up email with the accurate script to generate a random password.
Tim’s script is very interesting and informative as it not only corrects my errors but also provides few additional features which I was not able to provide in my original script.
Let us see the stored procedure first.
CREATE PROCEDURE GenerateRandomPwd1 (@length int = 20, @allowAtoZ BIT = 1, @allow0to9 BIT = 1, @allowSpecials1 BIT = 1, @allowSpecials2 BIT = 1, @avoidAmbiguousCharacters BIT = 1) AS BEGIN DECLARE @pwd VARCHAR(512) = '' --use master.dbo.spt_values as a pseudo tally(numbers) table SELECT TOP (@length) @pwd += CHAR(fn.rnd) FROM master.dbo.spt_values sv CROSS APPLY ( -- (max - min) + min SELECT [rnd] = (ABS(CHECKSUM(NEWID())) % (123 - 33)) + 33 ) fn WHERE (@avoidAmbiguousCharacters = 0 OR fn.rnd NOT IN (73, 108, 124, 79, 48, 49)) --capital i, lowercase l, vertical bar | capital o, the number(s) 0, 1 AND ( (@allowAtoZ = 1 AND ((fn.rnd >= 65 AND fn.rnd <= 90) OR (fn.rnd >= 97 AND fn.rnd <= 122))) OR (@allow0to9 = 1 AND (fn.rnd >= 48 AND fn.rnd <= 57)) OR (@allowSpecials1 = 1 AND (fn.rnd >= 33 AND fn.rnd <= 47)) OR (@allowSpecials2 = 1 AND (fn.rnd >= 58 AND fn.rnd <= 64)) ) SELECT [Password] = @pwd, [@allowAtoZ] = @allowAtoZ, [@allow0to9] = @allow0to9, [@allowSpecials1] = @allowSpecials1, [@allowSpecials2] = @allowSpecials2, [@avoidAmbiguousCharacters] = @avoidAmbiguousCharacters END GO
Now let us run the same stored procedure with few parameters.
EXEC GenerateRandomPwd1 @length = 20, @avoidAmbiguousCharacters = 0 EXEC GenerateRandomPwd1 @length = 20, @allow0to9 = 0, @allowSpecials1 = 0, @allowSpecials2 = 0, @avoidAmbiguousCharacters = 0 EXEC GenerateRandomPwd1 @length = 20, @allow0to9 = 0, @allowSpecials1 = 0, @allowSpecials2 = 0, @avoidAmbiguousCharacters = 1 EXEC GenerateRandomPwd1 @length = 20, @allowAtoZ = 0, @allow0to9 = 0, @allowSpecials1 = 1, @allowSpecials2 = 1
Well, you can see that you can specify various additional parameters and further customized your password. When I ran above script, I got the following results.
Thanks, Tim for amazing suggestions to generate a random password. If you have any similar or other interesting scripts which you use it for your daily routines, please send me an email at pinal@SQLAuthority.com and I will be happy to post the same with due credit to you. Remember sharing is caring! (Do not leave a comment as to avoid SQL Injections, it automatically parses out few of the special character and your comment may not visible)
Reference: Pinal Dave (https://blog.SQLAuthority.com)
Hi sir ,
while executing the Sp i getting an error as
Msg 102, Level 15, State 1, Procedure GenerateRandomPwd1, Line 18 [Batch Start Line 0]
Incorrect syntax near ‘;’.
gt and lt are actually > and < signs.
ABS(CHECKSUM(NEWID())) poses a problem. It crashes one time in 2^32. That might not be much of a problem here, but in a statement executed millions of times it would be dangerous. This can be demonstrated if applied in an infinite loop, which would crash within minutes. Use the modulos operand before the ABS to avoid trying to flip sign on int min.
This is better:
SELECT [rnd] = ABS(CHECKSUM(NEWID()) % (123 – 33)) + 33
Makes sense. Thanks for sharing.