Random password

How to generate a six character password.

You have to choose from a list of allowed characters (

(SELECT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ AS srcText , 62 as srcLen) AS S

)

select
empID as empID
,p.psw as psw1
,ca2.psw1+ ” +ca2.psw2+ ” +ca2.psw3+ ” +ca2.psw4+ ” +ca2.psw5+ ” +ca2.psw6 as psw2
–,len(ca2.psw1+ ” +ca2.psw2+ ” +ca2.psw3+ ” +ca2.psw4+ ” +ca2.psw5+ ” +ca2.psw6) as psw2Len
–,ca1.pozChar1,ca1.pozChar2,ca1.pozChar3,ca1.pozChar4,ca1.pozChar5,ca1.pozChar6
from dbo.Employees
CROSS APPLY
(SELECT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ AS srcText , 62 as srcLen) AS S
CROSS APPLY
(SELECT CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar1,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar2,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar3,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar4,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar5,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar6) as ca1
CROSS APPLY
(SELECT SUBSTRING(s.srcText,ca1.pozChar1,1) as psw1,
SUBSTRING(s.srcText,ca1.pozChar2,1) as psw2,
SUBSTRING(s.srcText,ca1.pozChar3,1) as psw3,
SUBSTRING(s.srcText,ca1.pozChar4,1) as psw4,
SUBSTRING(s.srcText,ca1.pozChar5,1) as psw5,
SUBSTRING(s.srcText,ca1.pozChar6,1) as psw6) as ca2

CROSS APPLY
(
    SELECT CHAR(ROUND(   (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' + 
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  ( 57-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) as psw
)p

Some very interesting script is here : https://stackoverflow.com/a/15038572/3992746

S