Random Password Generator

Here’s a script I created a while ago which generates a random password. It can be used to generate a password of variable length, incorporating symbols, numeric and lower and upper case characters. I’d really like to make it possible to join a recordset onto this by creating it as a function, but not sure where to go with it because of the rand() and newid() functions. Apparently including them in the SQL means I can’t create a function out of it. Could be something todo with the randomness being not random! But feel free to post any bugs, or improvements you find to this one.

/*
Created By: Dan Thompson
Date:		25 Aug 2011

Usage:
	EXEC  dbo.GeneratePassword @qtyUpperCase=1, @qtySymbols=1, @qtyNumeric=3, @passLength=10

Notes:
	Cannot be taken into UDF due to rand() and newid()
*/
IF OBJECT_ID('dbo.GeneratePassword') IS NOT NULL
	DROP PROCEDURE dbo.GeneratePassword

GO

CREATE PROCEDURE dbo.GeneratePassword(
	@qtyUpperCase	tinyint=1,
	@qtySymbols		tinyint=1,
	@qtyNumeric		tinyint=2,
	@passLength		int = 8
)
AS
BEGIN

	DECLARE @lowercase	varchar(26) = 'abcdefghijklmnopqrstuvwxyz',
			@uppercase	varchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
			@symbols	varchar(10) = '#@+?!£$^'

	DECLARE @qtyLowerCase	int	--Calculated

	SET @qtyLowerCase = @passLength - @qtyNumeric - @qtySymbols - @qtyUpperCase
	IF (@qtyLowerCase < 1)
	BEGIN
		RAISERROR('Cannot generate password. Check length variables',16,1)
		RETURN
	END

	DECLARE @tblPassword TABLE (i uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED DEFAULT newsequentialid(),
								 c varchar(10) NOT NULL)
	DECLARE @r tinyint = 0

	--lowercase
	WHILE ((SELECT count(*) FROM @tblPassword) < @qtyLowerCase)
	BEGIN
		SET @r = ROUND((len(@lowercase)-1)*rand(),0)+1

		INSERT INTO @tblPassword(c)
		SELECT substring(@lowercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols))
	BEGIN
		SET @r = ROUND((len(@symbols)-1)*rand(),0)+1

		INSERT INTO @tblPassword(c)
		SELECT substring(@symbols,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase))
	BEGIN
		SET @r = ROUND((len(@uppercase)-1)*rand(),0)+1

		INSERT INTO @tblPassword(c)
		SELECT substring(@uppercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase + @qtyNumeric))
	BEGIN
		INSERT INTO @tblPassword(c)
		SELECT CAST(ROUND(9*rand(),0) AS int)
	END

	DECLARE @pass as varchar(max) = ''

	SELECT @pass = @pass + p1.c
	FROM @tblPassword p1
	ORDER BY i

	SELECT @pass

END

I’d really like to come back to this and figure if I can include it in a function somehow. I was thinking some kind of row sampling might yield some kind of random seed using number tables, but i think that’s a long shot. Hopefully there will be a Part II to this in the future!