Have you ever needed to generate a random password in SQL Server? In a previous blog post, I shared a script to accomplish this task. However, thanks to the expertise of SQL Server expert Tim Cartwright, I realized that my script was incomplete. Tim graciously provided me with an updated script that not only corrects the errors in my original script but also includes additional features.

Let’s take a look at the stored procedure that Tim has shared:

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

Now, let’s run the stored procedure with different parameters to see the results:

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

As you can see, the stored procedure allows you to specify various parameters to customize the generated password. When I ran the above script, I obtained the following results. I am truly grateful to Tim for his amazing suggestions on generating random passwords.

If you have any similar or other interesting scripts that you use in your daily routines, please feel free to send them to me at pinal@SQLAuthority.com. I would be happy to post them on my blog, giving you due credit. Remember, sharing is caring!