Generating a Password in T-SQL from a Table of Words

By: Dallas Snider | Comments | Related: > Security

Problem

How can I use T-SQL to generate a password from a table of words?

Solution

In this tip, we will demonstrate how to write a T-SQL query that will create a password containing two words from a table of words, a number and a symbol. We will be utilizing the RANDBETWEEN function described in the tip Create Your Own RANDBETWEEN Function in T-SQL. Please review this tip first and create the function first.

We are starting with a table with a primary key column and a word column as shown below. There are 2,000 words in the table and they are sorted alphabetically with the first character in upper case.

-- create table
CREATE TABLE [dbo].[tblWordList](
	[pKey] [int] IDENTITY(1,1) NOT NULL,
	[word] [varchar](20) NULL,
 CONSTRAINT [PK_tblWordList] PRIMARY KEY CLUSTERED 
(
	[pKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--insert some data
INSERT INTO dbo.tblWordList (word)
SELECT 'Ability' UNION
SELECT 'Able' UNION
SELECT 'Aboard' UNION
SELECT 'About' UNION
SELECT 'Above' UNION
SELECT 'Accept' UNION
SELECT 'Accident' UNION
SELECT 'According' UNION
SELECT 'Account' UNION
SELECT 'Accurate'
GO

-- show inserted rows
SELECT * FROM dbo.tblWordList
GO

In the subquery in line 9 shown below, we are using the RANDBETWEEN function to generate a number between 1 and 2000 that is used in the WHERE clause to pick one word from the table. This is the first word in the password. We repeat the subquery code in line 10 to select the second word in the password. The two random words are concatenated in lines 4 and 5 of the query. In line 6 we concatenate a random number between 1 and 2000. In line 7, we use the RANDBETWEEN function to generate a random number between 35 and 38. These are the decimal ASCII codes for the hashtag (#) dollar sign ($), percent sign (%), and ampersand (&), respectively. We use the CHAR function to return the ASCII character represented by the decimal value returned by the RANDBETWEEN function. This produces the password shown at bottom of the figure below.

In the word list used for this tip, the minimum word length is 4 so we are always guaranteed a password with a minimum length of 11 (4 characters for the first word, 4 characters for the second word, 1 for the number and 1 for the symbol.)

The complete T-SQL code is listed below.

select w1.word + 
       w2.word + 
       cast(dbo.randbetween(1,2000) as varchar(4)) 
       + CHAR(dbo.randbetween(35,38)) as NewPassword
from 
(select word from tblWordList where pKey= [dbo].[randbetween](1,2000)) w1,
(select word from tblWordList where pKey= [dbo].[randbetween](1,2000)) w2

Next Steps

Make sure that your word table does not contain words that when combined with other words may be derogatory or offensive. You can easily adjust your word table and 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.

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

Generating a Password in T-SQL from a Table of Words

Problem

Solution

Next Steps

About the author

Comments For This Article