Home > SQL Server, T-SQL > Generate Strong Password in T-SQL

Generate Strong Password in T-SQL


 

Recently I needed to create a stored procedure to automate the creation of user logins within SQL Server and required a way to generate strong passwords. The following is the resultant code. It is fairly simplistic, but flexible and convenient.
It uses a recursive common table expression to create a number list and then uses the Crypt_Gen_Random function to generate a random number in the binary variable which is then used to dip into the character string to pull out the characters to build up the password. Due to the use of “For XML Path” characters which would be HTML encoded have been excluded.

 

Declare     @length int = 10
Declare     @chars char(92)
           ,@bytes binary(128)
           ,@password varchar(128)

Set         @chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!"£$%^*()_+-={[}]:;@~#,.?/'
Set         @bytes = Crypt_Gen_Random(128)
  
;With cte_numbers (number) As
(
    Select      Cast(1 As int)
    Union All
    Select      number + 1
    From        cte_numbers
    Where       number < @length
)
Select      @password = 
            (
                Select      SubString(@chars, (SubString(@bytes, n.Number, 1) % Len(@chars)) + 1, 1)
                From        cte_numbers n
                For         XML Path ('')
            )
            
-- display the resultant password
Select      @password
Advertisements
Categories: SQL Server, T-SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: