Generating A Password in SQL Server with T-SQL from Random Characters

written by Dallas Snider April 24, 2015 0 comments

Problem

How can I use T-SQL to generate a random password that meets my organization’s password requirements?

Solution

In this tip, we will show how to create your own user defined function in T-SQL that will return a randomly generated password. We will also demonstrate how you can customize the function to meet the password requirements for your organization. We will walk through sections of the T-SQL and then have a complete listing of the code at the end. For this example, we will create a password with a length of 10 that contains upper case and lower case letters, numbers and selected symbols. Furthermore, we will be utilizing the RANDBETWEEN function described in the tip Create Your Own RANDBETWEEN Function in T-SQL.

Let’s begin with the function declaration. We will pass no parameters to the function and we will return a varchar(10). The variable @randInt will hold the randomly generated decimal ASCII code value, @NewCharacter will hold the generated character, and the variable @NewPassword will hold the password. We initialize @NewPassword to an empty string in line 8.

In line 13, we call the RANDBETWEEN function to generate a value between 48 and 122. We use the IF statement in line 15 to restrict the character set used in the password. In line 17 we use the CHAR function to return the ASCII character represented by the decimal value. In line 18, we concatenate the new character to the password using the CONCAT function. The WHILE loop ensures this process is repeated until we have 6 characters.

In lines 23 and 24, we generate a random lower case letter and append it to the password string. In lines 27 and 28, we generate and append a random upper case letter. In lines 31 and 32, we generate and append a random number.

In lines 35 through 44, we are using a WHILE loop to append selected symbols to the password until the password has a length of 10. In line 46 we return our new password.

In the image below, we see the how to execute the function and an example of the password returned.

The complete T-SQL code is listed below.



CREATE FUNCTION dbo.GeneratePassword ()

RETURNS varchar(10)

AS

BEGIN

  DECLARE @randInt int;

  DECLARE @NewCharacter varchar(1);

  DECLARE @NewPassword varchar(10);

  SET @NewPassword=”;
  –6 random characters

  WHILE (LEN(@NewPassword) <6)
  BEGIN
    select @randInt=dbo.randbetween(48,122)
--      0-9           < = > ? @ A-Z [ \ ]                   a-z

    IF @randInt<=57 OR (@randInt>=60 AND @randInt<=93) OR (@randInt>=97 AND @randInt<=122)
    Begin
      select @NewCharacter=CHAR(@randInt)
      select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
    END
  END
  --Ensure a lowercase
  select @NewCharacter=CHAR(dbo.randbetween(97,122))
  select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
  
  --Ensure an upper case
  select @NewCharacter=CHAR(dbo.randbetween(65,90))
  select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
  
  --Ensure a number
  select @NewCharacter=CHAR(dbo.randbetween(48,57))
  select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
  
  --Ensure a symbol
  WHILE (LEN(@NewPassword) <10)
  BEGIN
    select @randInt=dbo.randbetween(33,64)
--           !               # $ % &                            < = > ? @

    IF @randInt=33 OR (@randInt>=35 AND @randInt<=38) OR (@randInt>=60 AND @randInt<=64) 
    Begin
     select @NewCharacter=CHAR(@randInt)
     select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
    END
  END
  RETURN(@NewPassword);
END;
GO
SELECT dbo.GeneratePassword() AS 'NewPassword';

Next Steps

You can adjust the T-SQL code above to meet the password requirements of your organization. Also, please check out these other tips and tutorials on T-SQL and the RAND() function on MSSQLTips.com.

Resources

Community

Subscribe