Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Create a complex password

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

Print | posted on Monday, September 29, 2008 2:35 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Create a complex password

Works like a charm. Thanks.
11/25/2008 2:14 PM | Ron Bracale
Gravatar

# re: Create a complex password

Feeling gratitude and not expressing it is like wrapping a present and not giving it. So it would be marked with the lot of enthusiasm and appreciation
5/3/2011 12:47 PM | casinot
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET