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

I hSQL SERVER -  How to Generate Random Password? - Enhanced Version - Part 2 randompassword ave 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://blog.SQLAuthority.com)

, , , , ,
Previous Post
SQL SERVER – COUNT, FROM and a Query – Interesting Observation
Next Post
SQL SERVER – Simple Method to Generate Fixed Digit OTP Code Using Random Function

Related Posts

10 Comments. Leave new

  • Tim Cartwright
    August 16, 2018 11:06 pm

    That’s an interesting change. I like it also. He used the tally to substring out the random chars. :)

    Reply
  • Seems nobody knows and uses very strong PRNG built into SQL Server – CRYPT_GEN_RANDOM() that generates far better randomness than this NEWID() hacks.

    Reply
  • Justin Terry (Syswright Limited)
    August 21, 2018 3:01 pm

    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.

    Reply
  • 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…

    Reply
  • SELECT NEWID()

    Reply

Leave a Reply

Menu