By: Brent Shaub | Last Updated: 2011-11-14 | Comments (31) | T-SQL
SQL Server is used to support many applications and one such feature of most applications is the storage of passwords. Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user. In this tip I cover a simple stored procedure to generate random passwords that can be incorporated into your applications.
The following stored procedure creates strings of random characters based on four parameters that configure the result.
- LEN - specifies the length of the result (required)
- MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
- RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74" (48 + 74 = 122) where 122 is a lowercase "z")
- EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>[email protected]`^\/)
Here is the stored procedure:
create proc [dbo].uspRandChars @len int, @min tinyint = 48, @range tinyint = 74, @exclude varchar(50) = '0:;<=>[email protected]`^\/', @output varchar(50) output as declare @char char set @output = '' while @len > 0 begin select @char = char(round(rand() * @range + @min, 0)) if charindex(@char, @exclude) = 0 begin set @output += @char set @len = @len - 1 end end ; go
To use the stored procedure issue commands such as the following.
declare @newpwd varchar(20)
-- all values between ASCII code 48 - 122 excluding defaults exec [dbo].uspRandChars @len=8, @[email protected] out select @newpwd
-- all lower case letters excluding o and l exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @[email protected] out select @newpwd
-- all upper case letters excluding O exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @[email protected] out select @newpwd
-- all numbers between 0 and 9 exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @[email protected] out select @newpwd
Here is sample output from the above commands:
- Unsure what ASCII codes to use, refer to this list of ASCII codes
- See where this procedure can be used in your application. Some examples include users inviting their friends or generating a new password by request or as a temporary replacement.
- Determine your environment's security policy including password length and valid characters.
- Implement your password policy in the parameters.
Last Updated: 2011-11-14
About the author
View all my tips