A SQL Script for generating random passwords from a given set of characters

GeneratePassword.sql

create table dbo.ValidPasswordChars
(
	IdConsecutiveSeedZero int not null identity(0,1) primary key clustered --as the name suggests; don't leave gaps.  
	, aChar char(1) collate Latin1_General_CS_AS not null 
)
go

--This creates the valid passwords as 0-9, a-z, A-Z.  Other characters can easily be added as needed; the table approach has been used to avoid hardcoding valid chars / relying on ascii ranges where the related system may not allow specific values within those ranges
;with cte(asciiCode) as
(
	select ascii(startChar)  
	from (values ('a'), ('A'), ('0')) x(startChar)
	union all
	select asciiCode + 1
	from cte
	where char(asciiCode) not in ('z','Z','9')
) 
insert into dbo.ValidPasswordChars (aChar)
select char(asciiCode) 
from cte 
go

--since we're only going with characters that can be typed it's probably OK to varchar this.  Potentially some countries/scenarios may wish to swap this nvarchar (and nchar where such references exist) 
--have to use a stored proce with an output parameter instead of functionsince rand is nondeterminative
create procedure dbo.GeneratePassword(@length int = 8, @password varchar(255) output) 
as
begin
	declare @result varchar(255) = '' 
	, @topRand int

	select @topRand = count(1) from dbo.ValidPasswordChars

	if not (@length between 0 and 255) set @length = @length / 0 --i.e. throw an error if we've been given invalid length
	while @length > 0
	begin
		select @result += cast(aChar as varchar(255))
		from dbo.ValidPasswordChars
		where IdConsecutiveSeedZero = floor(rand()*@topRand)
		set @length = @length - 1
	end
	set @password = @result
end
go

--example usage
declare @password8 varchar(255) --use 255 just so I can check all's good when the expected length is given
, @password12 varchar(12)       --use the actual length to ensure all's good when the required length is given
exec dbo.GeneratePassword 8, @password8 output
exec dbo.GeneratePassword 12, @password12 output
select @password8, @password12 --first test output: 0zyviwwn, jkiaxxyFih7d :)