Tag Archives: generate a password with sql

A picture with garbled characters and the word password in the middle

How to generate a random password with T-SQL

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , , , , ,

The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.

The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.

!
#
$
%
&
(
)
*
+

/*Declare Variables*/
DECLARE @i INT;
DECLARE @Pw VARCHAR(MAX);
DECLARE @Numbers TABLE (Characters CHAR(1));
DECLARE @LowerCase TABLE (Characters CHAR(1));
DECLARE @UpperCase TABLE (Characters CHAR(1));
DECLARE @Symbols TABLE (Characters CHAR(1));
DECLARE @BaseCharacters TABLE (Characters CHAR(1));
DECLARE @GuaranteedCharacters TABLE (Characters CHAR(1));
DECLARE @PwCharacters TABLE (Characters CHAR(1));

/*Generate Numbers*/
SET @i = 0;

WHILE @i <= 9
BEGIN
	INSERT INTO @Numbers
	SELECT @i

	SET @i = @i + 1
END;

/*Generate Lowercase Letters*/
SET @i = 97;

WHILE @i <= 122
BEGIN
	INSERT INTO @LowerCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Uppercase Letters*/
SET @i = 65;

WHILE @i <= 90
BEGIN
	INSERT INTO @UpperCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Symbols*/
SET @i = 33;

WHILE @i <= 43
BEGIN
	IF (
			@i = 34
			OR @i = 39
			)
	BEGIN
		SET @i = @i + 1
	END

	INSERT INTO @Symbols
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*
Randomly Select A Number, Lowercase Letter,
Uppercase Letter And A Symbol So Four Character Types
Are Guaranteed To Be Present Somewhere In The Password
*/
INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Numbers
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @LowerCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @UpperCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Symbols
ORDER BY NEWID();

/*
Randomly Select Another 6 Characters
*/
INSERT INTO @BaseCharacters
SELECT TOP 6 Characters
FROM (
	SELECT Characters
	FROM @Numbers
	
	UNION ALL
	
	SELECT Characters
	FROM @LowerCase
	
	UNION ALL
	
	SELECT Characters
	FROM @UpperCase
	
	UNION ALL
	
	SELECT Characters
	FROM @Symbols
	) AS Characters
ORDER BY NEWID()

/*Generate A 10 Character Password*/
INSERT INTO @PwCharacters (Characters)
SELECT Characters
FROM (
	SELECT Characters
	FROM @BaseCharacters
	
	UNION ALL
	
	SELECT Characters
	FROM @GuaranteedCharacters
	) AS Characters
ORDER BY NEWID()

/*Save The Password To A String*/
SELECT @Pw = COALESCE(@Pw + Characters, Characters)
FROM @PwCharacters

SELECT @Pw AS PW;

 

If you found this post helpful please like, comment and share.