Creating a Simple and Flexible Random Password Generator in SQL Server

Question

Creating a Simple and Flexible Random Password Generator in SQL Server

john.martin

Newbie

Points: 8
More actions
April 10, 2026 at 12:00 am

#4754609

Comments posted to this topic are about the item Creating a Simple and Flexible Random Password Generator in SQL Server

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004722 More actions · Answer 1

I appreciate anyone that steps up to the proverbial plate to share knowledge. Thank you for that.

The hard truth is that is an example of procedural thinking possibly being justified because RAND() always returns the same value for every row in a set without a seed.

You also don't have any method for preventing the rand spelling some really offensive words.

Please see the following article on how to quickly make random integers in a very old but nasty fast set based manner. Instead of using the INT formula there, use the following, instead, to prevent even the slightest chance of getting an infrequent error (one parenthesis moved)...

ABS(CHECKSUM(NEWID()) % @Range) + @StartValue

https://www.sqlservercentral.com/articles/generating-test-data-part-1-generating-random-integers-and-floats-1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas-282729 Right there with Babe Points: 721 More actions · Answer 2

As Jeff said, I appreciate anyone that contributes. I got to thinking about this and Jeff's quest to make things set-based. There's a relatively new function in SQL Server called Crypt_Gen_Random which would be superior to using the standard Rand() function. I whipped up something quick that would both be a quick password generator and be set-based.

Declare @length int = 30;
Declare @alphabet varchar(90) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
    + '!@#$%^&*()-_=+[]{};:,.<>/?';
Declare @base int = Len(@alphabet);
Declare @maxByte int = (256 / @base) * @base;

-- Generate extra bytes because some will be rejected
Declare @bytesNeeded int = @length * 2;
Declare @bytes varbinary(8000) = Crypt_Gen_Random(@bytesNeeded);

With E00(N) As ( Select 1 Union All Select 1 )
, E02(N) As ( Select 1 From E00 As E1 Cross Join E00 As E2 )
, E04(N) As ( Select 1 From E02 As E1 Cross Join E02 As E2 )
, E08(N) As ( Select 1 From E04 As E1 Cross Join E04 As E2 )
, E16(N) As ( Select 1 From E08 As E1 Cross Join E08 As E2 )
, E32(N) As ( Select 1 From E16 As E1 Cross Join E16 As E2 )
, Tally(N) As ( Select Row_Number() Over ( Order By N ) From E32 )
, raw As
    (
    Select N, Cast(Substring(@bytes, N, 1) As int) As byteNum
    From Tally
    Where N <= @bytesNeeded
    )
, accepted As
    (
    Select N, byteNum, Row_Number() Over( Order By N ) AS accepted_pos
    From raw
    Where byteNum < @maxByte  
    )
, chars As
    (
    Select accepted_pos, Substring(@alphabet, (byteNum % @base) + 1, 1) AS ch
    From accepted
    Where accepted_pos <= @length
    )
Select String_Agg(ch, '') Within Group( Order By accepted_pos ) As Password
From chars;