I needed to create a user-defined function (UDF) in SQL Server that returned a random password. My initial idea was to generate a random integer between some ASCII value ‘x’ and ASCII value ‘y,’ then concatenate several of these together and voila. Upon looking at the ASCII chart, I realized that there was no acceptable “block” of values that I wanted to use. I did not want to use punctuation marks or certain characters that are easily confused, such as: 0,o,O,l,1,i,I, etc. Additionally, to prevent an obscene word from being randomly created, I wanted to eliminate all vowels. So, my next thought was to create a look-up, cross reference table where a random ID value would correspond to a character in the table; however, this seemed like quite a bit of overhead for a simple user-defined function. Finally, I decided to use a single text variable to hold all of the desired characters. Then, I pulled random characters out of the variable base on the characters’ relative position inside the string of text.
The T-SQL is as follows:
Note that the following UDF (fn_GetRandomPassword) uses the function MyRand() previously discussed here:
http://www.dbtechie.com/sql-server/user-defined-functions-udf/random-thoughts-on-sql-server/
– =============================================
– Description: Create a random password of PasswordLength
– comprised of lowercase letters, not including [a,e,i,j,l,o,u] and
– including the numbers 2 through 8. Maximum length = 100 chars.
– =============================================
CREATE FUNCTION [dbo].[fn_GetRandomPassword]
(
@PasswordLength INT
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @retVal VARCHAR(100)
DECLARE @charList VARCHAR(26)
DECLARE @rnd FLOAT
DECLARE @loopCounter INT
DECLARE @randID INT
DECLARE @oneChar CHAR(1)
– build the charList with chars a-z
SET @charList = ”
SET @loopCounter=97
WHILE @loopCounter<123
BEGIN – while
SET @charList = @charList + CHAR(@loopCounter)
SET @loopCounter = @loopCounter + 1
END –while
–replace unwanted characters with numbers 2-8
–replace a
SET @charList = REPLACE(@charList,‘a’,‘2′)
–replace e
SET @charList = REPLACE(@charList,‘e’,‘3′)
–replace i
SET @charList = REPLACE(@charList,‘i’,‘4′)
–replace j
SET @charList = REPLACE(@charList,‘j’,‘5′)
–replace l
SET @charList = REPLACE(@charList,‘l’,‘6′)
–replace o
SET @charList = REPLACE(@charList,‘o’,‘7′)
–replace u
SET @charList = REPLACE(@charList,‘u’,‘8′)
– force passwordLength to be a legitimate value
IF @PasswordLength >100
SET @PasswordLength=100
IF @PasswordLength <1
SET @PasswordLength =1
– build random password
SET @loopCounter=0
SET @retVal = ”
WHILE @loopCounter < @PasswordLength
BEGIN – while
SET @rnd = (Select dbo.MyRand()) –Get random float from 0-1
SET @randID = ROUND(25 * @rnd,0) + 1 –Get random int from 1 to 26
SET @oneChar = SUBSTRING(@charList,@randID,1)
SET @retVal = @retVal + @oneChar
SET @loopCounter = @loopCounter + 1
END – while
RETURN @retVal
END
