CREATE PROCEDURE dbo.uspCreatePassword
(
@UpperCaseItems SMALLINT,
@LowerCaseItems SMALLINT,
@NumberItems SMALLINT,
@SpecialItems SMALLINT
)
AS
SET NOCOUNT ON
-- Initialize some variables
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 @UpperCaseItems < -20
SET @UpperCaseItems = -20
IF @LowerCaseItems > 20
SET @LowerCaseItems = 20
IF @LowerCaseItems < -20
SET @LowerCaseItems = -20
IF @NumberItems > 20
SET @NumberItems = 20
IF @NumberItems < -20
SET @NumberItems = -20
IF @SpecialItems > 20
SET @SpecialItems = 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