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

August 15, 2018

I have previously written about Random Password before in the blog posts here, here and here. This blog is sent by James Curran, who has created the following generate random password stored procedure keeping the base of the original solution of Tim Cartwright. Thanks to both of this individual SQL Server Expert who has helped me to come up with a more robust solution for a random password. James has built upon the original logic of Tim but changed few lines to get more clarity to new T-SQL beginners.

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://darkslategrey-bat-805937.hostingersite.com)