SQL Server script to generate passwords

May 14, 2012 | | Tags : SQL Tools Scripts


As a DBA and partially a sysadmin, I quite often need to create strong passwords. While there are many tools available, they are not necessarily installed on my desktop, but SSMS is. So to simplify my life I wrote this simple script to generate passwords with each symbol appear in the password one time only. I haven’t built any additional checks to make sure that uses all types of symbols in the password (i.e. letters, numbers and special characters), but if the password looks not so strong, I can always rerun the script.

Here it is:

SET NOCOUNT ON

DECLARE 
 @chars varchar (1000) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*',
 @password varchar (16) = '',
 @charpos int,
 @pos int = 0

DECLARE
 @len int = LEN(@chars);

WHILE (@pos < 16)
BEGIN
 SET @charpos = CONVERT(int, RAND(CHECKSUM(NEWID())) * @len);
 SET @password += SUBSTRING(@chars, @charpos + 1, 1);

-- SELECT @chars, @charpos, LEFT (@chars, @charpos), SUBSTRING(@chars, @charpos + 2, 100), SUBSTRING(@chars, @charpos + 1, 1), @len;

 SET @chars = LEFT (@chars, @charpos) + SUBSTRING(@chars, @charpos + 2, 100);
 SET @len = LEN(@chars);
 SET @pos += 1;
END

SELECT @password;

Comments