How To Generate Random Strings in SQL | GUID and Passwords

Random strings, characters and ID’s are used all the time in development and especially in Database work where some data needs to be unique or uniquely identified. This example code was written many years ago to generate passwords in SQL 2008 with certain complexities for automation purposes using the RAND() function.

I’m sure it can be improved upon with a little time and focus but here it is for those interested.

For those that are just looking for a global universal non-repeating ID (GUID), you can use NewID() in SQL Server.

Using NEWID() for a Unique Identifier | GUID

This ID can be used for resetting a password or validating a RESTful API access or just to hash a single record and guarantee it has not been modified.

SELECT NEWID() 

-- AF2CAD10-0686-490E-AEF7-42368F584281

---

Random Password Generation Requirements

For this code we want to control all of the parameters such as the string length, characters used, upper and lower case and more. Since we are creating multiple passwords we (db tales com) will also need to put them into a table variable so we can save each and loop to the next.

Parameter Requirements:

• Max Length
• Upper and Lower Characters
• Iteration count
• Number of Passwords to Generate

Declare the needed variables for control…

DECLARE
	@pswdcount INT,  
	@maxlength INT,  
	@character VARCHAR(2),  -- character list
	@paswd VARCHAR(20),     --  password generated
	@Upper INT,
	@Lower INT,
	@iteration INT,
	@iterationcount INT,
	@position INT

Table Variable

The iteration variable is used to define the number of passwords to create. We also need a Table variable to store each password as we loop through them.

DECLARE @paswds TABLE ( 
     id INT IDENTITY(1,1), 
     pword VARCHAR(20) 
)

The main part of this script is the iterations or looping that is done for each character, then password. We use a WHILE statement to control the iteration or number of passwords to be created.
Then we need another WHILE loop to generate the password and save each to our Temp Table variable.

WHILE (@iterationcount <= @iteration)  -- start iterations
BEGIN
	WHILE(@pswdcount <= @maxlength)  -- start passwords
		BEGIN

Using RAND() and a Character String

The key portion of or random generation is using the RAND() statement against a list of characters that we have approved to be used in a password. The idea is to automatically create a password that satisfies the SQL Server complexity requirements.

SELECT @position = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SELECT @character = SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%*[]()', @position, 1)

SET @pswdcount = @pswdcount + 1
SET @paswd = @paswd + @character

Defining the characters explicitly allows us to exclude problem characters such as the Ampersand which can cause problems in a connection string.

After all of the password characters have been assigned to our @paswd variable, we save it and reset the variables so we can go through the next iteration.

INSERT INTO @paswds SELECT @paswd -- save this paswd  -- save to table variable

SET @iterationcount = @iterationcount + 1     -- next iteration
SET @pswdcount = 1  -- reset @pswdcount
SET @paswd = ''     -- reset @paswd to nothing

Simple SQL Code

Here is the complete T-SQL code for this example…

DECLARE
	@pswdcount INT,
	@maxlength INT,
	@character VARCHAR(2),
	@paswd VARCHAR(20),
	@Upper INT,
	@Lower INT,
	@iteration INT,
	@iterationcount INT,
	@position INT

SET @paswd = ''
SET @maxlength = 20  --- length of paswd
SET @Lower = 1 ---- The lowest random number
SET @Upper = 72 ---- The highest random number
SET @iteration = 10  ---- number of passwds to create
SET @iterationcount = 1
SET @pswdcount = 1

DECLARE @paswds TABLE ( id INT IDENTITY(1,1), pword VARCHAR(20) )

WHILE (@iterationcount <= @iteration)  -- start iterations
	BEGIN
		WHILE(@pswdcount <= @maxlength)  -- start passwords
			BEGIN

				SELECT @position = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
				SELECT @character = SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%*[]()', @position, 1)

				SET @pswdcount = @pswdcount + 1
				SET @paswd = @paswd + @character

			END

		INSERT INTO @paswds SELECT @paswd -- save this paswd
		SET @iterationcount = @iterationcount + 1
		SET @pswdcount = 1  -- reset @pswdcount
		SET @paswd = ''  -- reset @paswd
	END

SELECT * FROM @paswds

---