Generate random Password

SQL Server is a robust back end for many application. Sometimes it is necessary to generate random password for Users in some application. Here is a stored procedure that generate random string based on some parameters:


CREATE PROC GenerateRandomPass
@Start TINYINT = 48,
@Range TINYINT = 74,
@ExChar VARCHAR(35) = '\/<=>0[]O:@;?^`',
@LenOfPass INT,
@Output VARCHAR(35) OUTPUT
AS
DECLARE @String CHAR
SET @Output = ''
    WHILE @LenOfPass > 0 
BEGIN
  SELECT @String = CHAR(ROUND(RAND()* @Range + @Start, 0))
  if CHARINDEX(@String, @ExChar) = 0 
  BEGIN
  SET @Output = @Output + @String
  SET @LenOfPassOfPass = @LenOfPassOfPass - 1
END
END
GO;


Here
@Start means starting ASCII char, Default is 48 which is 0.
@Range means how many ASCII chars to include. Default is 74 (48+74 = 122) which is z.
@ExChar means characters to exclude from output.
@LenOfPass means length of generated string.


Example
DECLARE @Password varchar(10)
-- All Upper Case Letters Excluding @ and ^
EXEC [dbo].GenerateRandomPass @Start=65, @Range=25, @ExChar='@^', @LenOfPass=10, @Output=@Password OUT
SELECT @Password
-- All Lower Case Letters Excluding 0 and l
EXEC [dbo].GenerateRandomPass @Start=97, @Range=25, @ExChar='0l', @LenOfPass=10, @Output=@Password OUT
SELECT @Password
-- All numbers between 0 and 9
EXEC [dbo].GenerateRandomPass @Start=48, @Range=9, @ExChar='', @LenOfPass=10, @Output=@Password OUT
SELECT @Password
-- All Values Between ASCII code 48 - 122 excluding defaults
EXEC [dbo].GenerateRandomPass @LenOfPass=10, @Output=@Password OUT
SELECT @Password