Generate Random Complex Passwords in T-SQL
Submitted by Andy on Fri, 12/16/2005 - 01:00.
The following sproc will generate a random complex password, and return it as an output parameter from the caller. Complex means it will be guaranteed to contain one upper and one lower case letter, one number 0-9, and one special character.
CREATE PROCEDURE spRandomPassword
(
@len int = 8,
@password varchar(25) OUTPUT
)
AS
BEGIN
DECLARE @type tinyint, @bitmap char(6), @score int, @curlen int
IF (@len < 6)
BEGIN
RAISERROR ('Minimum @len is 6', 16, 1)
END
START_OVER:
SET @password=''
SET @score = 0
SET @curlen = 0
WHILE @len > @curlen
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
BEGIN
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
SET @score = 1 | @score
END
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
BEGIN
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
SET @score = 2 | @score
END
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
BEGIN
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
SET @score = 4 | @score
END
ELSE IF @type = 4 --Appending a random special character to @password
BEGIN
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
SET @score = 8 | @score
END
SET @curlen = @curlen + 1
END
-- Check to ensure that one upper, one lower, one number and special character are in the password
PRINT @score
IF (@score != 15)
BEGIN
-- Start over
GOTO START_OVER
END
ELSE
SELECT @password
END
| Attachment | Size |
|---|---|
| spRandomPassword.sql | 1.36 KB |
I have tried to run this
I have tried to run this script that you created, however, I'm receiving the following error -
Server: Msg 8114, Level 16, State 4, Procedure uspRandomPassord, Line 0
Error converting data type nvarchar to int -
Here is the syntax, and my execution command -
CREATE PROCEDURE uspRandomPassword
(
@len int = 8,
@password varchar(25) OUTPUT
)
AS
BEGIN
DECLARE @type tinyint, @bitmap char(6), @score int, @curlen int
IF (@len < 6)
BEGIN
RAISERROR ('Minimum @len is 6', 16, 1)
END
START_OVER:
SET @password=''
SET @score = 0
SET @curlen = 0
WHILE @len > @curlen
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
BEGIN
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
SET @score = @score + 1
END
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
BEGIN
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
SET @score = @score + 2
END
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
BEGIN
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
SET @score = @score + 4
END
ELSE IF @type = 4 --Appending a random special character to @password
BEGIN
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
SET @score = @score + 8
END
SET @curlen = @curlen + 1
END
-- Check to ensure that one upper, one lower, one number and special character are in the password
PRINT @score
IF (@score != 15)
BEGIN
-- Start over
GOTO START_OVER
END
ELSE
SELECT @password
END
exec uspRandomPassword Mike
My environment is MS SQL 2000
Can you help with this issue? Or explain why the error is occuring?
You should not be passing in
You should not be passing in a string. This routine just generates a random varchar string to be used as a password. To actual reset the login named Mike's password with this, it would look something like the following: