Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

SQL Server - T-SQL Password generator

Discussion in 'SQL Server Scripts and Tools' started by Peter Schmitz, Oct 17, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    While trying to write another script, I suddenly had a need for a password generating script for SQL Server. I previously wrote one in C#, but figured it might also be useful having a T-SQL version of it, so I set off to write one.

    I ended up doing it as a stored procedure, which in the script below is added to the master-database. I will also create a CLR version some time very soon.

    The script is nothing fancy, but it might show a few tricks to newcomers in T-SQL. Note it adds parameters with default values to the procedure, as well as an output parameter, it uses a very crude randomizer, and shows a SQL syntax that might look familiar to those who worked in a C-like language before, by using the += syntax.

    Instead of using:

    Code (text):

    IF @lowerCaseBit = 1 SET @workingSet += @lowerCaseChar;
    I could also have used:

    Code (text):

    IF @lowerCaseBit = 1 SET @workingSet = @workingSet + @lowerCaseChar;
    It would have yielded the same result, but I have to admit that I do prefer the first syntax, because it looks cleaner, and it means less typing ;-)

    Hope this helps someone. You might soon see it referenced again in another script I'm working on :)

    Code (text):

    CREATE PROCEDURE [dbo].[uspGeneratePassword]
            @passwordLength smallint = 8, @lowerCaseBit bit = 1, @upperCaseBit
     bit = 1, @numberBit bit = 1, @specialBit bit = 1, @generatedPassword
     varchar(128) OUTPUT
     *       Procedure to generate passwords
     *       Author: Peter Schmitz, SchmitzIT
     *       Date: 07-Jun-2011
     *       Version: 0.99
     *               Parameters:
     *                       @passwordLength         =       The desired password length
     *                       @lowercaseBit           =       Should the password include lowercase characters?
     *                       @uppercaseBit           =       Should the password include uppercase characters?
     *                       @numberBit                      =       Should the password include numbers?
     *                       @specialBit                     =       Should the password include special characters?
     *                       @generatedPassword      =       Output parameter containing the generated password
     *               Usage:
     *                       DECLARE @password varchar(128)
     *                       EXECUTE dbo.uspGeneratePassword 10, 1, 1, 1, 1, @password OUTPUT
     -- Variables holding the characters to be used
     DECLARE @lowerCaseChar char(26) = 'abcdefghijklmnopqrstuvwxyz',
            @upperCaseChar char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
            @numberChar char(10) = '01234567889',
            As per BOL (see topic: strong passwords):
            If used in an OLE DB or ODBC connection string, a login or password
     must not contain the following characters: [] {}() , ; ?
            These characters are used to either initialize a connection or
     separate connection values.
            @specialChar char(33) = '`~#$%^&-_=+\\|:\"<.>/?';
     -- Placeholder for password. 128 is the maximum length allowed for passwords.
     DECLARE @password varchar(128) = N'';
     -- String together the characters to be used for generating the password.
     -- It is set to be maximum 95 characters (26 + 26 + 10 + 33)
     DECLARE @workingSet nvarchar(95) = N'';
     IF @lowerCaseBit = 1 SET @workingSet += @lowerCaseChar;
     IF @upperCaseBit = 1 SET @workingSet += @upperCaseChar;
     IF @numberBit = 1 SET @workingSet += @numberChar;
     IF @specialBit = 1 SET @workingSet += @specialChar;
     -- Now that we have a set of characters, let's generate some random numbers.
     DECLARE @i tinyint = 1;
     WHILE @i <= @passwordLength
            SET @password += SUBSTRING(@workingSet, CONVERT(int, 1 +
     (LEN(@workingSet) * RAND())), 1)
            SET @i += 1;
     SET @generatedPassword = @password;

Share This Page