|
I've played around with similar scripts, this code shows three different versions. One difference is that I eliminated WHILE loops, and another is that I use random numbers to select from a string of allowable characters rather than using the CHAR() function. This avoids dealing with ASCII character code values, and if you use NVARCHAR lists you could throw in some Unicode characters.
The first segment generates a 10-char password with one call to NEWID().
The next piece is a view that creates random integers based on NEWID(). This allows its use in multi-row SELECT statements, where RAND() would return the same value for every row. As noted in the comments, this would be better as a function but NEWID() cannot be used in a function due to side effects. (The only point of this view is to avoid repeating the CAST(SUBSTRING(CAST())) expression in the following two procedures.)
The third fragment will generate a random password of the desired length from the given character set.
The final fragment allows specifying password complexity parameters, i.e. specifying counts individually for uppercase, lowercase, digits, and punctuation.
/* Simple random password generator
*/
-- Allowable chars are 2-9, A-Z (except I and O), and a-z (except l)
-- Math purists may point out that the character set length should be a power of two (32, 64, 128) so each char
-- is equally probable, otherwise a subset (the first 256 % LEN chars) are slightly more likely. The default
-- set is 64 chars long and includes some punctuation, but excludes characters that might be misread ( 1 I l and 0 O ).
DECLARE @charset VARCHAR(256)
SET @charset = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789$-#+_&;'
-- Generate the password from one call to NEWID()
DECLARE @newid BINARY(16)
SET @newid = NEWID()
-- Build the hash string by using 10 bytes of the GUID to select allowable chars.
-- The binary bytes are not used in CHAR() functions, so no ASCII value arithmetic is needed.
-- Each byte is used to select a character from @charset, which could be NVARCHAR if you wanted to include non-ASCII characters
DECLARE @hash CHAR(10)
SET @hash = ''
SELECT @hash = STUFF(@hash, number, 1, SUBSTRING(@charset, (CAST(SUBSTRING(@newid, 14-number, 1) AS INT) % LEN(@charset)) + 1, 1))
FROM ( SELECT number
FROM master.dbo.spt_values
WHERE type='P' AND number BETWEEN 1 AND 10 ) x
PRINT @hash
-----------------------------------------------------------------------------------------------------------------------------
/* Random integer generating view
Using NEWID() rather than RAND() allows set-based queries instead of one-char-at-a-time WHILE loops.
A view can be used to extract an integer from the NEWID() uniqueidentifier.
(A function would be better, but NEWID() is not legal in functions due to side effects.)
*/
GO
-- Return a positive 32-bit integer taken from bytes 12-15 of a NEWID() uniqueidentifier
CREATE VIEW dbo.Rand32 AS
SELECT RandInt = CAST(SUBSTRING(CAST(NEWID() AS BINARY(16)), 12, 4) AS INT) & 0x7FFFFFFF
GO
-----------------------------------------------------------------------------------------------------------------------------
/* This procedure generates passwords up to 50 characters long, taken from the character set of your choosing.
Math purists may point out that the character set length should be a power of two (32, 64, 128) so each char
is equally probable, otherwise a subset (the first 256 % LEN chars) are slightly more likely. The default
set is 64 chars long and includes some punctuation, but excludes characters that might be misread ( 1 I l and 0 O ).
*/
GO
-- Create a random password
CREATE PROC dbo.GenerateRandomPassword
@len INT = 10,
@charset VARCHAR(256) = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789$-#+_&;',
@pwd VARCHAR(50) OUTPUT
AS
SET @pwd = '' ;
SELECT @pwd = @pwd + SUBSTRING(@charset, (RandInt % LEN(@charset)) + 1, 1)
FROM master.dbo.spt_values
CROSS JOIN dbo.Rand32
WHERE type = N'P' AND number BETWEEN 1 AND @len ;
GO
DECLARE @pwd VARCHAR(99) ;
EXEC dbo.GenerateRandomPassword @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateRandomPassword @len = 15, @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateRandomPassword @len = 8, @charset = '-=[]\;,./abcdefghijkmnopqrstuvwxyz23456789', @pwd = @pwd OUTPUT ; PRINT @pwd
-----------------------------------------------------------------------------------------------------------------------------
/* This procedure generates random passwords that meet complexity requirements for different classes of characters.
The individual character sets are not included as parameters, but could be if you feel that is necessary.
*/
GO
-- Create passwords that meet specific complexity rules
-- Parameters specify how many upper case, lower case, numeric, and punctuation characters to include
-- Characters are chosen randomly from each group, and mixed together in a random order
CREATE PROC dbo.GenerateComplexPassword
@uc INT = 4,
@lc INT = 4,
@num INT = 3,
@punc INT = 2,
@pwd VARCHAR(50) OUTPUT
AS
SET NOCOUNT ON ;
DECLARE @uc_chars VARCHAR(50),
@lc_chars VARCHAR(50),
@num_chars VARCHAR(50),
@punc_chars VARCHAR(50) ;
SELECT @uc_chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ',
@lc_chars = 'abcdefghijkmnopqrstuvwxyz',
@num_chars = '23456789',
@punc_chars = '`~!@#$%^&*()_-+={[}]\:;"''<,>.?/',
@pwd = '' ;
SELECT @pwd = @pwd + chr
FROM (
SELECT chr = SUBSTRING(@uc_chars, (RandInt % LEN(@uc_chars)) + 1, 1),
ord = NEWID()
FROM master.dbo.spt_values
CROSS JOIN dbo.Rand32
WHERE type = N'P' AND number BETWEEN 1 AND @uc
UNION ALL
SELECT chr = SUBSTRING(@lc_chars, (RandInt % LEN(@lc_chars)) + 1, 1),
ord = NEWID()
FROM master.dbo.spt_values
CROSS JOIN dbo.Rand32
WHERE type = N'P' AND number BETWEEN 1 AND @lc
UNION ALL
SELECT chr = SUBSTRING(@num_chars, (RandInt % LEN(@num_chars)) + 1, 1),
ord = NEWID()
FROM master.dbo.spt_values
CROSS JOIN dbo.Rand32
WHERE type = N'P' AND number BETWEEN 1 AND @num
UNION ALL
SELECT chr = SUBSTRING(@punc_chars, (RandInt % LEN(@punc_chars)) + 1, 1),
ord = NEWID()
FROM master.dbo.spt_values
CROSS JOIN dbo.Rand32
WHERE type = N'P' AND number BETWEEN 1 AND @punc
) c
ORDER BY ord
GO
DECLARE @pwd VARCHAR(99) ;
EXEC dbo.GenerateComplexPassword @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateComplexPassword @uc = 8, @lc = 2, @num = 0, @punc = 0, @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateComplexPassword @uc = 1, @lc = 8, @num = 1, @punc = 1, @pwd = @pwd OUTPUT ; PRINT @pwd
|
Brent Shaub has been creating, renovating and administering databases since 1998.