Generating A Password in SQL Server with T-SQL from Random Characters
By: Dallas Snider | Updated: 2015-04-24 | Comments (4) | Related: More > Security
How can I use T-SQL to generate a random password that meets my organization's password requirements?
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';
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
Last Updated: 2015-04-24
About the author
View all my tips