Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > Security


SQL Server Conference Giveaway - click to learn more


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.

Word table

-- 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.

First six characters

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.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools