Sql Password Generator - 8 characters, upper and lower, and include a number

Question

I need to create a new password in TSQL, which must contain 8 characters (az), which must contain the upper and lower characters, as well as at least one number.

Can anyone help me with this?

Answer 1

select cast((Abs(Checksum(NewId()))%10) as varchar(1)) + char(ascii('a')+(Abs(Checksum(NewId()))%25)) + char(ascii('A')+(Abs(Checksum(NewId()))%25)) + left(newid(),5) from yourTable 

Answer 2

Something to consider, a little more complicated:

 SELECT TOP 8 SUBSTRING(tblSource.vsSource, tblValue.number + 1, 1) AS vsChar FROM (SELECT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' AS vsSource) AS tblSource JOIN master..spt_values AS tblValue ON tblValue.number < len(tblSource.vsSource) WHERE tblValue.type = 'P' ORDER BY NEWID() 

but very random.

Answer 3

Here is one option that gives you very strong random passwords. It uses the numbers table, but not so simple.

 declare @chars char(62), @bytes binary(8) set @chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' set @bytes = CRYPT_GEN_RANDOM(8) select SUBSTRING(@chars, (SUBSTRING(@bytes, n.Number, 1) % LEN(@chars)) + 1, 1) from dbo.Numbers n where n.Number between 1 and LEN(@bytes) FOR XML PATH ('')