SQL Server 產生隨機密碼：預存程序與函數實作範例

需求背景

DBA 或系統管理員常需批次產生符合複雜度要求的隨機密碼（如建立測試帳號、重設使用者密碼）。手工編寫既慢又易重複，透過 T-SQL 封裝可重複使用、參數化控制長度與數量。

方案一：批次產生預存程序 sp_RandomPWDkbd_yu

改良自 SQLServerCentral 社群經典程式碼，支援一次產生多組密碼。

CREATE PROCEDURE dbo.sp_RandomPWDkbd_yu
    @Length  INT = 8,   -- 密碼長度
    @Count   INT = 1    -- 產生筆數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Chars VARCHAR(100) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*';
    DECLARE @i INT = 1, @j INT, @Pwd VARCHAR(128), @Result TABLE (Pwd VARCHAR(128));

    WHILE @i <= @Count
    BEGIN
        SET @Pwd = '';
        SET @j = 1;
        WHILE @j <= @Length
        BEGIN
            -- 取亂數索引 (1-based)
            SET @Pwd = @Pwd + SUBSTRING(@Chars, 
                CAST(RAND(CHECKSUM(NEWID())) * LEN(@Chars) AS INT) + 1, 1);
            SET @j = @j + 1;
        END
        INSERT @Result VALUES (@Pwd);
        SET @i = @i + 1;
    END
    SELECT Pwd FROM @Result;
END
GO

呼叫範例

EXEC dbo.sp_RandomPWDkbd_yu 12, 20;  -- 產生 20 組 12 碼密碼

方案二：標量函數 fn_RandomPassword 單筆產生

CREATE FUNCTION dbo.fn_RandomPassword(@Length INT = 8)
RETURNS VARCHAR(128)
AS
BEGIN
    DECLARE @Chars VARCHAR(100) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*';
    DECLARE @Pwd VARCHAR(128) = '', @i INT = 1;
    WHILE @i <= @Length
    BEGIN
        SET @Pwd = @Pwd + SUBSTRING(@Chars, 
            CAST(RAND(CHECKSUM(NEWID())) * LEN(@Chars) AS INT) + 1, 1);
        SET @i = @i + 1;
    END
    RETURN @Pwd;
END
GO

批次產生技巧：搭配系統表

-- 利用 sys.objects 產生 N 列，再套用函數
SELECT TOP 50 
    name AS ObjectName, 
    dbo.fn_RandomPassword(10) AS RandomPwd
FROM sys.objects;

進階需求：符合密碼策略（大小寫、數字、特殊字元至少各一）

CREATE FUNCTION dbo.fn_StrongPassword(@Length INT = 12)
RETURNS VARCHAR(128)
AS
BEGIN
    IF @Length < 4 SET @Length = 4;
    DECLARE @Lower VARCHAR(26) = 'abcdefghijklmnopqrstuvwxyz';
    DECLARE @Upper VARCHAR(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE @Digit VARCHAR(10) = '0123456789';
    DECLARE @Spec  VARCHAR(10) = '!@#$%^&*';
    DECLARE @All   VARCHAR(72) = @Lower + @Upper + @Digit + @Spec;
    DECLARE @Pwd VARCHAR(128) = '';

    -- 保證四類各至少一個
    SET @Pwd = @Pwd + SUBSTRING(@Lower, CAST(RAND(CHECKSUM(NEWID())) * 26 AS INT) + 1, 1);
    SET @Pwd = @Pwd + SUBSTRING(@Upper, CAST(RAND(CHECKSUM(NEWID())) * 26 AS INT) + 1, 1);
    SET @Pwd = @Pwd + SUBSTRING(@Digit, CAST(RAND(CHECKSUM(NEWID())) * 10 AS INT) + 1, 1);
    SET @Pwd = @Pwd + SUBSTRING(@Spec,  CAST(RAND(CHECKSUM(NEWID())) * 10 AS INT) + 1, 1);

    -- 填充剩餘長度
    DECLARE @i INT = 5;
    WHILE @i <= @Length
    BEGIN
        SET @Pwd = @Pwd + SUBSTRING(@All, CAST(RAND(CHECKSUM(NEWID())) * 72 AS INT) + 1, 1);
        SET @i = @i + 1;
    END

    -- 打亂順序（簡易 Fisher-Yates）
    DECLARE @Arr TABLE (Idx INT IDENTITY(1,1), Ch CHAR(1));
    DECLARE @k INT = 1;
    WHILE @k <= LEN(@Pwd)
    BEGIN
        INSERT @Arr VALUES (SUBSTRING(@Pwd, @k, 1));
        SET @k = @k + 1;
    END
    SET @Pwd = '';
    SELECT @Pwd = @Pwd + Ch FROM @Arr ORDER BY NEWID();
    RETURN @Pwd;
END
GO

效能與安全性注意事項

• RAND() 種子問題：單一批次中直接用 RAND() 會產生相同序列，必須搭配 CHECKSUM(NEWID()) 或 CRYPT_GEN_RANDOM (2008+) 產生不可預測種子。
• 密碼儲存：產生後務必立即雜湊儲存（BCrypt/Argon2/PBKDF2），絕不可明文寫入資料表或日誌。
• 傳輸加密：應用層取得密碼後應透過 TLS 傳遞給使用者（如寄送一次性連結而非密碼本身）。
• 大量產生效能：單筆函數呼叫開銷較大，百萬筆以上建議改用 CLR 函數或應用層批次產生再 BULK INSERT。

結語

T-SQL 內建即可滿足一般隨機密碼產生需求，關鍵在於亂數種子處理與密碼策略強制。生產環境請搭配雜湊儲存、加密傳輸、審計日誌，構建完整的密碼生命週期管理。