Generating temporary passwords is a common requirement in many applications. Users may need to reset a forgotten password, new accounts may require an initial password, or developers may generate test credentials. While password generation is often implemented in application code, doing it directly in SQL Server provides clear advantages. It centralizes the logic, ensures consistency across applications, and simplifies maintenance.

In this article, we will build a flexible stored procedure that generates random passwords of any length, allows precise control over character selection, and can exclude confusing or unwanted characters. The implementation is straightforward and practical for real-world use.

Understanding the Core Concept

Each character in SQL Server has a numeric representation known as an ASCII value. Common ranges for password generation include numbers (0–9, ASCII 48–57), uppercase letters (A–Z, ASCII 65–90), and lowercase letters (a–z, ASCII 97–122).

The main idea is simple: generate a random number within a defined ASCII range, convert it to a character using CHAR(), and append it to the password. Before adding a character, the procedure checks it against an exclusion list to skip any confusing or unwanted characters. This loop continues until the password reaches the requested length.

How the Algorithm Works

The procedure begins by validating that the requested password length is greater than zero. An empty string is initialized to hold the password, and a loop runs until the password reaches the desired length.

Within each iteration, a random number is generated within the specified ASCII range and converted into a character. If the character exists in the exclusion list, it is skipped. Otherwise, it is added to the password string. Because some characters may be excluded, the loop may run slightly more times than the password length to ensure only valid characters are included.

The Stored Procedure

Here is the complete implementation of the password generator. Input validation and clear parameter naming are included for clarity.

CREATE OR ALTER PROCEDURE dbo.GenerateRandomPassword
(
    @Length        INT,                              -- Required password length
    @AsciiStart    TINYINT = 48,                     -- Starting ASCII value
    @AsciiRange    TINYINT = 74,                     -- Number of ASCII values to include
    @ExcludeChars  VARCHAR(50) = '0O:;<=>?@[]^\/',  -- Characters to exclude
    @Password      VARCHAR(100) OUTPUT               -- Generated password
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate password length
    IF @Length IS NULL OR @Length <= 0
    BEGIN
        RAISERROR('Password length must be greater than zero.', 16, 1);
        RETURN;
    END

    DECLARE @RandomNumber INT;
    DECLARE @RandomChar   CHAR(1);

    SET @Password = '';

    -- Generate password
    WHILE LEN(@Password) < @Length
    BEGIN
        SET @RandomNumber = FLOOR(RAND() * (@AsciiRange + 1)) + @AsciiStart;
        SET @RandomChar = CHAR(@RandomNumber);

        IF CHARINDEX(@RandomChar, @ExcludeChars) = 0
        BEGIN
            SET @Password = @Password + @RandomChar;
        END
    END
END
GO

Understanding the Parameters

The procedure provides flexible parameters to tailor password generation. @Length defines how many characters the password will contain and is validated at the start. @AsciiStart sets the starting ASCII value for character selection, commonly 48 (0), 65 (A), or 97 (a). @AsciiRange determines the number of consecutive ASCII values available for random selection from the starting point.

@ExcludeChars is a string of characters to omit from the password. By default, it excludes visually confusing characters such as 0 and O. @Password is the output variable that returns the generated password. Adjusting these parameters allows you to produce numeric, lowercase, uppercase, or mixed passwords according to specific rules.

Testing in SQL Server Management Studio

These examples can be run directly in SSMS. Each execution produces a different password due to pseudo-random generation.

Default Settings (Letters and Numbers)

This generates an 8-character password using numbers and letters, with default exclusions.

DECLARE @NewPassword VARCHAR(20);

EXEC dbo.GenerateRandomPassword
     @Length = 8,
     @Password = @NewPassword OUTPUT;

SELECT @NewPassword AS GeneratedPassword;

Possible output:

vKZehKeL

Lowercase Letters Only (Excluding 'o' and 'l')

Generates a 10-character password using only lowercase letters, skipping 'o' and 'l'.

DECLARE @NewPassword VARCHAR(20);

EXEC dbo.GenerateRandomPassword
     @Length = 10,
     @AsciiStart = 97,
     @AsciiRange = 25,
     @ExcludeChars = 'ol',
     @Password = @NewPassword OUTPUT;

SELECT @NewPassword AS GeneratedPassword;

Possible output:

rmpypjqkgb

Uppercase Letters Only (Excluding 'O')

Generates a 12-character password using uppercase letters, skipping 'O'.

DECLARE @NewPassword VARCHAR(20);

EXEC dbo.GenerateRandomPassword
     @Length = 12,
     @AsciiStart = 65,
     @AsciiRange = 25,
     @ExcludeChars = 'O',
     @Password = @NewPassword OUTPUT;

SELECT @NewPassword AS GeneratedPassword;

Possible output:

PVYNNPVJNLXM

Numbers Only

Generates a 14-digit numeric password without exclusions.

DECLARE @NewPassword VARCHAR(20);

EXEC dbo.GenerateRandomPassword
     @Length = 14,
     @AsciiStart = 48,
     @AsciiRange = 9,
     @ExcludeChars = '',
     @Password = @NewPassword OUTPUT;

SELECT @NewPassword AS GeneratedPassword;

Possible output:

26180479446079

About Randomness

The procedure uses SQL Server’s RAND() function to produce pseudo-random numbers. This approach is suitable for temporary or low-risk system-generated passwords. However, RAND() is not a cryptographically secure generator. For high-security scenarios, consider using stronger functions such as CRYPT_GEN_RANDOM() to generate more secure random values.

Why This Approach Works

Centralizing password generation in SQL Server has multiple benefits. It ensures consistency across applications, provides flexibility to adjust character sets and exclusions, simplifies maintenance, and allows reuse in multiple scenarios. The procedure balances beginner-friendly simplicity with production-ready adaptability.

Conclusion

This stored procedure provides a clear, flexible, and practical way to generate random passwords in SQL Server. By combining ASCII ranges, pseudo-random numbers, and exclusion logic, it can produce numeric, lowercase, uppercase, or mixed passwords tailored to your needs. It is easy to test, understand, and adapt, making it ideal for applications requiring temporary or system-generated passwords.