Every DBA needs to generate passwords for various purposes and here is a stored procedure you can use to generate customized passwords. It is very easy to change to include more character groups or lessen the present characters in a group. The default is 4 groups of characters; Upper case, Lower case, Numbers and Special characters.

You call the stored procedure with the number of characters you want from each group with a parameter. There is an extra feature built-in the code! If you do not want duplicate characters from a group but still want three characters from Numbers, use a negative parameters with the value of -3.

Full control over a customized random-generated password seems a contradiction, but it is not. With this stored procedure you have full control over the creation process with no effort!

Let us go through how this procedure works. First of all, create the stored procedure header.

CREATE PROCEDURE dbo.uspCreatePassword
(
 @UpperCaseItems SMALLINT,
 @LowerCaseItems SMALLINT,
 @NumberItems SMALLINT,
 @SpecialItems SMALLINT
)
AS

The stored procedure accepts four parameters, one for each group of significant characters. In this stored procedure I have included UpperCase items, LowerCase items, Number items and Special items. You can, if you want to, change these groups to other items to better fit your purposes.

The reason these parameters are SMALLINTs is that you can pass the value 2, as well as -2. If you pass a positive value of 2, you will get 2 characters from that group. You will have no control over if duplicate characters are output, such as double E. If you do not want duplicate characters from a group, pass a negative value such as -2. Then a double E

combination is not possible.

Next, we tell SQL Server to not output the number of affected rows

SET NOCOUNT ON

Now we need some local variables. We need four variables to hold the groups of significant characters to use. We also need to declare a temporary variable to

hold the selected characters from each group as well as a loop counter(@i) and

a character variable (@c) and a position variable (@v).

DECLARE  @UpperCase VARCHAR(26),
  @LowerCase VARCHAR(26),
  @Numbers VARCHAR(10),
  @Special VARCHAR(13),
  @Temp VARCHAR(8000),
  @Password VARCHAR(8000),
  @i SMALLINT,
  @c VARCHAR(1),
  @v TINYINT

With these variables set, we now need to set the default characters for each group. We also initialize the @Temp and @Password variable to an empty string.

-- Set the default items in each group of characters
SELECT  @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  @LowerCase = 'abcdefghijklmnopqrstuvwxyz',
  @Numbers = '0123456789',
  @Special = '!@#$%&*()_+-=',
  @Temp = '',
  @Password = ''

If, for some reason, the wanted number of characters for a group is set to a very high number, limit the number to a maximum.

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
  SET @UpperCaseItems = 20
IF @LowerCaseItems > 20
  SET @LowerCaseItems = 20
IF @NumberItems > 20
  SET @NumberItems = 20
IF @SpecialItems > 20
  SET @SpecialItems = 20

We need to do the selection for UpperCase items. Since the parameter can be either positive or negative, set the loop counter to the absolute value of characters wanted from that group.

-- Get the Upper Case Items
 SET @i = ABS(@UpperCaseItems)

As long as the loop counter is greater than zero, loop. But only as long as there are characters left to choose from!

This second condition is only needed for negative values, because this insures that no duplicate characters can be chosen.

WHILE @i > 0 AND LEN(@UpperCase) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1,
     @c = SUBSTRING(@UpperCase, @v, 1),
     @UpperCase = CASE WHEN @UpperCaseItems < 0 THEN
STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END,
     @Temp = @Temp + @c,
    @i = @i - 1

The first SELECT gets a random number with the help of NEWID() function. A NEWID() value returns a signed 128-bit binary number, formatted with hexadecimal numbers. We need to CAST that value to binary and then CAST again to BIGINT in order to do calculations with it.

What we do next is to take the absolute value with ABS function and take the modula value with the MOD function denoted with % in SQL Server! The reason for taking the modula value for the random number is that it returns a value between 0 and the length of the numbers available in that group. And we add 1 to that value since strings of characters in SQL Server begins with position 1. The second SELECT is to get the character selected.

The third SELECT is the trick! If a negative number is passed to the stored procedure, we now need to discard the selected character with the STUFF function, so that the character can not be chosen again.

The fourth SELECT simply concatenates the @Temp variable with the chosen character. The fifth and last SELECT is to decrease the loop counter.

For the next group of characters, Lower Case items, do exactly as with the Upper Case items.

-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)
WHILE @i > 0 AND LEN(@LowerCase) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1,
    @c = SUBSTRING(@LowerCase, @v, 1),
    @LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END,
    @Temp = @Temp + @c,
    @i = @i - 1

For the next group of characters, Number items, do exactly as with the Upper Case items.

-- Get the Number Items
SET  @i = ABS(@NumberItems)
WHILE @i > 0 AND LEN(@Numbers) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1,
    @c = SUBSTRING(@Numbers, @v, 1),
    @Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END,
    @Temp = @Temp + @c,
    @i = @i - 1

For the next group of characters, Special items, do exactly as with the Upper Case items.

-- Get the Special Items
SET  @i = ABS(@SpecialItems)
WHILE @i > 0 AND LEN(@Special) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1,
    @c = SUBSTRING(@Special, @v, 1),
    @Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END,
    @Temp = @Temp + @c,
    @i = @i - 1

The drawback right now with this algorithm is that all the Upper Case items are first, Lower Case items are second, Number items are third and Special items are fourth.

Now we need to reposition these chosen characters. This is done with the code above. First get a random character from the @Temp string and add that to the @Password string. Next, remove the selected character and repeat the process until there are no more characters to reposition!

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1,
    @Password = @Password + SUBSTRING(@Temp, @v, 1),
    @Temp = STUFF(@Temp, @v, 1, '')

The last thing to do, is to output the randomly generated password

SELECT  @Password

The complete stored procedure is here

CREATE PROCEDURE dbo.uspCreatePassword(    
  @UpperCaseItems SMALLINT
  ,    @LowerCaseItems SMALLINT
  ,    @NumberItems SMALLINT
  ,    @SpecialItems SMALLINT)
 AS  
 SET NOCOUNT ON 
 DECLARE @UpperCase VARCHAR(26)
        , @LowerCase VARCHAR(26)
  , @Numbers VARCHAR(10)
  , @Special VARCHAR(13)
  , @Temp VARCHAR(8000)
  , @Password VARCHAR(8000)
  , @i SMALLINT
  , @c VARCHAR(1)
  , @v TINYINT 
-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    ,    @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
 ,    @Numbers = '0123456789'
 ,    @Special = '!@#$%&*()_+-='
 ,    @Temp = ''
 ,    @Password = '' 
-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
  SET @UpperCaseItems = 20 
IF @LowerCaseItems > 20
  SET @LowerCaseItems = 20 
IF @NumberItems > 20    
  SET @NumberItems = 20 
IF @SpecialItems > 20    
  SET @SpecialItems = 20 
-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems) 
WHILE @i > 0 AND LEN(@UpperCase) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1
  ,    @c = SUBSTRING(@UpperCase, @v, 1)
  ,    @UpperCase = CASE 
       WHEN @UpperCaseItems < 0 
    THEN STUFF(@UpperCase, @v, 1, '') 
 ELSE @UpperCase 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems) 
WHILE @i > 0 AND LEN(@LowerCase) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1
  ,    @c = SUBSTRING(@LowerCase, @v, 1)
  ,    @LowerCase = CASE 
       WHEN @LowerCaseItems < 0 
   THEN STUFF(@LowerCase, @v, 1, '') 
 ELSE @LowerCase 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
-- Get the Number Items
SET @i = ABS(@NumberItems) 
WHILE @i > 0 AND LEN(@Numbers) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1
  ,    @c = SUBSTRING(@Numbers, @v, 1)
  ,    @Numbers = CASE 
       WHEN @NumberItems < 0 
   THEN STUFF(@Numbers, @v, 1, '') 
 ELSE @Numbers 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
  
-- Get the Special Items
SET @i = ABS(@SpecialItems) 
WHILE @i > 0 AND LEN(@Special) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1
  ,    @c = SUBSTRING(@Special, @v, 1)
  ,    @Special = CASE 
       WHEN @SpecialItems < 0 
   THEN STUFF(@Special, @v, 1, '') 
    ELSE @Special 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
  
-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1
  ,    @Password = @Password + SUBSTRING(@Temp, @v, 1)
  ,    @Temp = STUFF(@Temp, @v, 1, '') 
SELECT @Password