SQL Server - T-SQL Password generator

[Peter Schmitz]

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:

IF @lowerCaseBit = 1 SET @workingSet += @lowerCaseChar;

I could also have used:

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

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
 */
 
 AS
 
 -- 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
 BEGIN
        SET @password += SUBSTRING(@workingSet, CONVERT(int, 1 +
 (LEN(@workingSet) * RAND())), 1)
 
        SET @i += 1;
 END
 
 SET @generatedPassword = @password;
 
 GO