SQL SERVER – How to Generate Random Password? – Enhanced Version

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)