Generate Random Password Using SQL Stored Procedure

This is a small blog on how to generate random password in sql server stored procedure itself. 

 

Sometime we need to generate password on our own without knowing user what it will be.

 

Below is very simple SQL code which will help you to generate a strong random password.

CREATE PROCEDURE GENERATEPWD(@OUTMESSAGE VARCHAR(10) OUTPUT)
AS
BEGIN
SET NOCOUNT ON
declare @LENGTH INT,@CharPool varchar(26),@PoolLength varchar(26),@LoopCount  INT
DECLARE @RandomString VARCHAR(10),@CHARPOOLINT VARCHAR(9)


SET @CharPool = 'A!B@C!D#E@FG#H$IJ$K%LM%N*PQR%ST&U*V(W)X_YZ'
DECLARE @TMPSTR VARCHAR(3)

SET @PoolLength = DataLength(@CharPool)
SET @LoopCount = 0
SET @RandomString = ''

    WHILE (@LoopCount <10)
    BEGIN
        SET @TMPSTR =   SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength), 1)
        SELECT @RandomString  = @RandomString + CONVERT(VARCHAR(5), CONVERT(INT, RAND() * 10))
        IF(DATALENGTH(@TMPSTR) > 0)
        BEGIN
            SELECT @RandomString = @RandomString + @TMPSTR
            SELECT @LoopCount = @LoopCount + 1
        END
    END
    SET @LOOPCOUNT = 0
    SET @OUTMESSAGE=@RandomString
END

Above procedure will return 10 digit length of alpha-numeric password.

 

PARAMETER EXPLANATION

 

@CharPool = Contain alphabets (Random you can change as you wish)

 

You can edit this character pool to include numbers in the password if you want. 

 

You can see a while loop running up to count of 10.

 

Inside that loop we are fetching 1 character randomly from character pool and appends it to a variable at each iteration.

 

Finally it will assign the result to @OUTMESSAGE variable which is of type VARCHAR(10) OUTPUT.

 

And you will get the random 10 digit long random password very easily.

 

Hope it helps someone.