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

By: Dallas Snider   |   Comments (4)   |   Related: > Security

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.

• A More Versatile SQL Server Random Number Function
• Different ways to get random data for SQL Server data sampling
• Create Your Own RANDBETWEEN Function in T-SQL
• Generating Random Numbers in SQL Server Without Collisions
• SQL Server Random Sorted Result Set
• Our complete tutorial list

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips