SQL SERVER – How to Generate Random Password? – Enhanced Version

Earlier last month, I posted an interview question – How to Generate Random Passwords in SQL Server? – Interview Question of the Week #181. Though, my efforts were honest my answer was not complete in that blog post. Thankfully, SQL Server Expert Tim Cartwright noticed the error in the blog post and decided to send me to follow up email with the accurate script to generate a random password.

SQL SERVER - How to Generate Random Password? - Enhanced Version randompassword

Tim’s script is very interesting and informative as it not only corrects my errors but also provides few additional features which I was not able to provide in my original script.

Let us see the stored procedure first.

CREATE PROCEDURE GenerateRandomPwd1 (@length int = 20,
@allowAtoZ BIT = 1,
@allow0to9 BIT = 1,
@allowSpecials1 BIT = 1,
@allowSpecials2 BIT = 1,
@avoidAmbiguousCharacters BIT = 1) AS
BEGIN
DECLARE @pwd VARCHAR(512) = ''
--use master.dbo.spt_values as a pseudo tally(numbers) table
SELECT TOP (@length) @pwd += CHAR(fn.rnd)
FROM master.dbo.spt_values sv
CROSS APPLY (
-- (max - min) + min
SELECT [rnd] = (ABS(CHECKSUM(NEWID())) % (123 - 33)) + 33
) fn
WHERE (@avoidAmbiguousCharacters = 0 OR fn.rnd NOT IN (73, 108, 124, 79, 48, 49)) --capital i, lowercase l, vertical bar | capital o, the number(s) 0, 1
AND (
(@allowAtoZ = 1 AND ((fn.rnd >= 65 AND fn.rnd <= 90) OR (fn.rnd >= 97 AND fn.rnd <= 122)))
OR (@allow0to9 = 1 AND (fn.rnd >= 48 AND fn.rnd <= 57))
OR (@allowSpecials1 = 1 AND (fn.rnd >= 33 AND fn.rnd <= 47))
OR (@allowSpecials2 = 1 AND (fn.rnd >= 58 AND fn.rnd <= 64))
)
SELECT [Password] = @pwd,
[@allowAtoZ] = @allowAtoZ,
[@allow0to9] = @allow0to9,
[@allowSpecials1] = @allowSpecials1,
[@allowSpecials2] = @allowSpecials2,
[@avoidAmbiguousCharacters] = @avoidAmbiguousCharacters
END
GO

Now let us run the same stored procedure with few parameters.

EXEC GenerateRandomPwd1 @length = 20, @avoidAmbiguousCharacters = 0
EXEC GenerateRandomPwd1 @length = 20, @allow0to9 = 0, @allowSpecials1 = 0, @allowSpecials2 = 0, @avoidAmbiguousCharacters = 0
EXEC GenerateRandomPwd1 @length = 20, @allow0to9 = 0, @allowSpecials1 = 0, @allowSpecials2 = 0, @avoidAmbiguousCharacters = 1
EXEC GenerateRandomPwd1 @length = 20, @allowAtoZ = 0, @allow0to9 = 0, @allowSpecials1 = 1, @allowSpecials2 = 1

Well, you can see that you can specify various additional parameters and further customized your password. When I ran above script, I got the following results.

SQL SERVER - How to Generate Random Password? - Enhanced Version randompass

Thanks, Tim for amazing suggestions to generate a random password. If you have any similar or other interesting scripts which you use it for your daily routines, please send me an email at pinal@SQLAuthority.com and I will be happy to post the same with due credit to you. Remember sharing is caring! (Do not leave a comment as to avoid SQL Injections, it automatically parses out few of the special character and your comment may not visible)

Reference: Pinal Dave (https://blog.SQLAuthority.com)

, , , ,
Previous Post
SQL SERVER – T-SQL Script: How to Search for Multiple Values in ERRORLOG?
Next Post
SQL SERVER – Script – Turn off Firewall Remotely (PowerShell, NetSh, PsExec)

Related Posts

4 Comments. Leave new

  • Hi sir ,

    while executing the Sp i getting an error as

    Msg 102, Level 15, State 1, Procedure GenerateRandomPwd1, Line 18 [Batch Start Line 0]
    Incorrect syntax near ‘;’.

    Reply
  • ABS(CHECKSUM(NEWID())) poses a problem. It crashes one time in 2^32. That might not be much of a problem here, but in a statement executed millions of times it would be dangerous. This can be demonstrated if applied in an infinite loop, which would crash within minutes. Use the modulos operand before the ABS to avoid trying to flip sign on int min.

    This is better:

    SELECT [rnd] = ABS(CHECKSUM(NEWID()) % (123 – 33)) + 33

    Reply

Leave a Reply

Menu