Creation of random password which is easy to remember!!

Question

Creation of random password which is easy to remember!!

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
June 2, 2015 at 10:53 am

#304562

Hi Experts,
I have written a script to GENERATE Random Password. But I want to modify that to a some kind of password which is little easy to remember!
I have seen some of the online sites where they generate passwords which are easy to remember and having some way to remember them,
my script:-
declare @db_pswd nvarchar(4000);
declare @len1 int = 16,
@min tinyint = 40,
@range tinyint = 74,
@exclude varchar(50) = '11cdtyuXR#0:;<=>?@O[]`^\/abfty#$%^&1234567890*',
@output varchar(500)
declare @char char
set @output = ''
while @len1 > 0 begin
select @char = char(round(rand() * @range + @min, 0))
if charindex(@char, @exclude) = 0 begin
set @output += @char
set @len1 = @len1 - 1
end
end
Print @output
Can anyone please suggest something on top of my script or something interesting beyond my script?
Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why are you trying to reinvent the wheel? There are numerous password generators that will be able to follow different rules according to necessities (ensuring the use of upper and lower cases, numbers, symbols, avoiding repeated or sequence letters or numbers, different lengths, etc).

Your exclude string seems partially random as you're removing certain letters but not on one case or another. Reducing the options will just reduce the security. I'm not sure if I'd go through that path.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323546 More actions · Answer 2

For assigning an easy to remember initial password, i like to use a dictionary plus a couple of random numbers.

using a table of animal names, HTML Color Names or things like that work real well.

i use something like this to update multiple rows quickly:

;With MyColorCTE AS

( SELECT 'AliceBlue' AS TheColor UNION ALL

SELECT 'AntiqueWhite' UNION ALL SELECT 'Aqua' UNION ALL SELECT 'Aquamarine' UNION ALL

SELECT 'Azure' UNION ALL SELECT 'Beige' UNION ALL SELECT 'Bisque' UNION ALL SELECT 'Black' UNION ALL

SELECT 'BlanchedAlmond' UNION ALL SELECT 'Blue' UNION ALL SELECT 'BlueViolet' UNION ALL SELECT 'Brown' UNION ALL

SELECT 'BurlyWood' UNION ALL SELECT 'CadetBlue' UNION ALL SELECT 'Chartreuse' UNION ALL SELECT 'Chocolate' UNION ALL

SELECT 'Coral' UNION ALL SELECT 'CornFlowerBlue' UNION ALL SELECT 'Cornsilk' UNION ALL SELECT 'Crimson' UNION ALL

SELECT 'Cyan' UNION ALL SELECT 'DarkBlue' UNION ALL SELECT 'DarkCyan' UNION ALL SELECT 'DarkGoldenrod' UNION ALL

SELECT 'DarkGray' UNION ALL SELECT 'DarkGreen' UNION ALL SELECT 'DarkKhaki' UNION ALL SELECT 'DarkMagenta' UNION ALL

SELECT 'DarkOliveGreen' UNION ALL SELECT 'DarkOrange' UNION ALL SELECT 'DarkOrchid' UNION ALL SELECT 'DarkRed' UNION ALL

SELECT 'DarkSalmon' UNION ALL SELECT 'DarkSeaGreen' UNION ALL SELECT 'DarkSlateBlue' UNION ALL SELECT 'DarkSlateGray' UNION ALL

SELECT 'DarkTurquoise' UNION ALL SELECT 'DarkViolet' UNION ALL SELECT 'DeepPink' UNION ALL SELECT 'DeepSkyBlue' UNION ALL

SELECT 'DimGray' UNION ALL SELECT 'DodgerBlue' UNION ALL SELECT 'FireBrick' UNION ALL SELECT 'FloralWhite' UNION ALL

SELECT 'ForestGreen' UNION ALL SELECT 'Fuchsia' UNION ALL SELECT 'Gainsboro' UNION ALL SELECT 'GhostWhite' UNION ALL

SELECT 'Gold' UNION ALL SELECT 'Goldenrod' UNION ALL SELECT 'Gray' UNION ALL SELECT 'Green' UNION ALL

SELECT 'GreenYellow' UNION ALL SELECT 'Honeydew' UNION ALL SELECT 'HotPink' UNION ALL SELECT 'IndianRed' UNION ALL

SELECT 'Indigo' UNION ALL SELECT 'Ivory' UNION ALL SELECT 'Khaki' UNION ALL SELECT 'Lavender' UNION ALL

SELECT 'LavenderBlush' UNION ALL SELECT 'LawnGreen' UNION ALL SELECT 'LemonChiffon' UNION ALL SELECT 'LightBlue' UNION ALL

SELECT 'LightCoral' UNION ALL SELECT 'LightCyan' UNION ALL SELECT 'LightGray' UNION ALL SELECT 'LightGreen' UNION ALL

SELECT 'LightPink' UNION ALL SELECT 'LightSalmon' UNION ALL SELECT 'LightSeaGreen' UNION ALL SELECT 'LightSkyBlue' UNION ALL

SELECT 'LightSlateGray' UNION ALL SELECT 'LightSteelBlue' UNION ALL SELECT 'LightYellow' UNION ALL SELECT 'Lime' UNION ALL

SELECT 'LimeGreen' UNION ALL SELECT 'Linen' UNION ALL SELECT 'Magenta' UNION ALL SELECT 'MidnightBlue' UNION ALL

SELECT 'Maroon' UNION ALL SELECT 'MediumAquamarine' UNION ALL SELECT 'MediumBlue' UNION ALL SELECT 'MediumVioletRed' UNION ALL

SELECT 'MediumOrchid' UNION ALL SELECT 'MediumTurquoise' UNION ALL SELECT 'MediumPurple' UNION ALL SELECT 'MediumSpringGreen' UNION ALL

SELECT 'MediumSeaGreen' UNION ALL SELECT 'MediumSlateBlue' UNION ALL SELECT 'MintCream' UNION ALL SELECT 'MistyRose' UNION ALL

SELECT 'Moccasin' UNION ALL SELECT 'NavajoWhite' UNION ALL SELECT 'Navy' UNION ALL SELECT 'OldLace' UNION ALL

SELECT 'Olive' UNION ALL SELECT 'OliveDrab' UNION ALL SELECT 'Orange' UNION ALL SELECT 'OrangeRed' UNION ALL

SELECT 'Orchid' UNION ALL SELECT 'PaleGoldenrod' UNION ALL SELECT 'PaleGreen' UNION ALL SELECT 'PaleTurquoise' UNION ALL

SELECT 'PaleVioletRed' UNION ALL SELECT 'PapayaWhip' UNION ALL SELECT 'PeachPuff' UNION ALL SELECT 'Peru' UNION ALL

SELECT 'Pink' UNION ALL SELECT 'Plum' UNION ALL SELECT 'PowderBlue' UNION ALL SELECT 'Purple' UNION ALL

SELECT 'Red' UNION ALL SELECT 'RosyBrown' UNION ALL SELECT 'RoyalBlue' UNION ALL SELECT 'SaddleBrown' UNION ALL

SELECT 'Salmon' UNION ALL SELECT 'SandyBrown' UNION ALL SELECT 'SeaGreen' UNION ALL SELECT 'Seashell' UNION ALL

SELECT 'Sienna' UNION ALL SELECT 'Silver' UNION ALL SELECT 'SkyBlue' UNION ALL SELECT 'SlateBlue' UNION ALL

SELECT 'SlateGray' UNION ALL SELECT 'Snow' UNION ALL SELECT 'SpringGreen' UNION ALL SELECT 'SteelBlue' UNION ALL

SELECT 'Tan' UNION ALL SELECT 'Teal' UNION ALL SELECT 'Thistle' UNION ALL SELECT 'Tomato' UNION ALL

SELECT 'Turquoise' UNION ALL SELECT 'Violet' UNION ALL SELECT 'Wheat' UNION ALL SELECT 'White' UNION ALL

SELECT 'WhiteSmoke' UNION ALL SELECT 'Yellow' UNION ALL SELECT 'YellowGreen' )

,CTERANDOMLYNUMBER AS

(

SELECT

ROWNUM = ROW_NUMBER() OVER ( ORDER BY NEWID()) ,

TheColor + LEFT(ABS(CHECKSUM(TheColor)),2) As RND,*

FROM MyColorCTE --THE TABLE WITH THE KEY

)

SELECT * FROM CTERANDOMLYNUMBER

/*

UPDATE MyTarget

SET MyTarget InitialPassword = case when len(RND) <8 then 'SlateGray312' else rnd end --8 character minimum

from Users MyTarget

inner join CTERANDOMLYNUMBER

on MyTarget.UserID % 139 + 1 = rownum --139 random records from the colors.

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Thnx Lowell for giving me the approach and the nice script indeed. Many thnx.

Hi Luis

I can go to online site to steal their password if incase I have a one time need. What will I do if I need to automate any prgram where I need to generate a random password and there are possibility that people might do mistake of using ',<,>,? etc which may not work properly in XML based applications unless guys really know to do type casting ,etc

I given just an random example of my case, so a script posted by Lowell could help a lot as my script is generating such a password which is really tough for anyone to recall or think/

Good day to all of you....!!!!

Thanks.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

SQL-DBA-01 (6/2/2015)
Thnx Lowell for giving me the approach and the nice script indeed. Many thnx.
Hi Luis
I can go to online site to steal their password if incase I have a one time need. What will I do if I need to automate any prgram where I need to generate a random password and there are possibility that people might do mistake of using ',<,>,? etc which may not work properly in XML based applications unless guys really know to do type casting ,etc
I given just an random example of my case, so a script posted by Lowell could help a lot as my script is generating such a password which is really tough for anyone to recall or think/
Good day to all of you....!!!!

To be honest, selecting "easy to remember passwords" is a no-no. When you have a new user, temporarily give them a password and force them to change it. I prefer mostly numeric passwords for such temporary passwords with the occasional symbol and only 1 or 2 letters (depending on your password policy) so that I don't have to worry about accidently spelling out an offensive password.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)