SQL Password Generator to Create Random Password
To create random password in SQL Server, T-SQL developer can use random password generator stored procedure source codes shared in this SQL tutorial. If programmers require to initialize or create random passwords for applications (like required during registration process), by customizing password generation process in Generate_Password stored procedure. SQL database administrator and SQL programmers can cover different password complexity requirements for randomly generated passwords using SQL Password Generator stored procedure.
SQL random password generator procedure titled Generate_Password returns the created password using the procedure parameter @pwd marked as OUTPUT procedure.
OUTPUT hint enables procedure to return data back to the calling code block.
Please check below T-SQL syntax used for running SQL procedure with output parameters.
DECLARE @password varchar(25)
EXECUTE Generate_Password @password OUTPUT
SELECT @password
Besides SQL stored procedure Generate_Password, we need a database table named PasswordCharacter to store valid characters acceptable within a password created randomly. Here is the DDL for the master data table which is a base for the password characters.
create table PasswordCharacter (
item nchar(1),
[numeric] bit,
[letter] bit,
uppercase bit,
lowercase bit,
special bit
)
insert into PasswordCharacter(item, [letter], uppercase) values ('A',1,1)
insert into PasswordCharacter(item, [letter], uppercase) values ('B',1,1)
insert into PasswordCharacter(item, [letter], uppercase) values ('C',1,1)
insert into PasswordCharacter(item, [letter], lowercase) values ('a',1,1)
insert into PasswordCharacter(item, [letter], lowercase) values ('b',1,1)
insert into PasswordCharacter(item,[numeric]) select '0',1
insert into PasswordCharacter(item,[numeric]) select '1',1
insert into PasswordCharacter(item,special) select '*',1
insert into PasswordCharacter(item,special) select '_',1
As seen above, I only added a few samples for possible valid characters that can be used in a password using SQL INSERT statements.
You can use complete alphabet, numbers and add more special characters like >, | or +, etc. to generate more complex passwords in SQL using the password generator whose source codes are shared in this tutorial.
Please download SQL script which contains valid characters for random password generator in SQL.
Below is the T-SQL source codes of our SQL Server password generator procedure named Generate_Password to create random password for SQL applications.
This password generator stored procedure is used to create random passwords in varying length between 8 and 14 character long. Of course this password length characters can be altered by changing the @len_min and @len_max parameters used in the Generate_Password SQL stored procedure. @len_min and @len_max are used for minimum password length and maximum password length.
declare @len_min smallint -- minimum length for valid password
declare @len_max smallint -- maximum length for valid password
set @len_min = 8
set @len_max = 14
And SQL password generator randomly chooses an integer value between min and max length boundaries by running the following SQL command.
The first line randomly sets a value between 0 and 6 (@len_max - @len_min) after multiplication result is converted to smallint variable @len.
I add the minimum password length and reach a figure between given size limits.
select @len = RAND( CAST(NEWID() AS VARBINARY) ) * (1 + @len_max - @len_min)
select @len = @len + @len_min
Transact-SQL developers will realize I used SQL RAND() function to create a random numeric value between 0 and 1 (actually it can not be 1) But programmers have to feed SQL Server RAND function with a seed value to enable random function to generate different values each time it is executed. So the best SEED parameter for random RAND() SQL function is "CAST(NEWID() AS VARBINARY)".
In order to explain further steps for how SQL password creator procedure chooses characters within the password, first I want to expain that a valid password is assumed to be formed of upper case, lower case characters, numbers and special characters like *
I assume that random generated password should contain at least one item from these 4 character groups (upper case, lower case, numeric and special character categories).
So at the beginning of the sql stored procedure code, I decide how many characters from each category will be included in the generated random password.
select @U_cnt = RAND( CAST(NEWID() AS VARBINARY) ) * (@len - (@L_cnt + @N_cnt + @S_cnt)) + 1
select @L_cnt = RAND( CAST(NEWID() AS VARBINARY) ) * (@len - (@U_cnt + @N_cnt + @S_cnt)) + 1
select @N_cnt = RAND( CAST(NEWID() AS VARBINARY) ) * (@len - (@U_cnt + @L_cnt + @S_cnt)) + 1
select @S_cnt = @len - (@U_cnt + @L_cnt + @N_cnt)
If you select these internal parameter values in your stored procedure, you can see how many characters will be choosen from each category. The second multiplier in each assignment statement ensures that a random length will be assigned and the total length will be equal to previously defined password length.
The following code block within the random password generator procedure select each time 1 chracter from each category using TOP 1 and ORDER BY NEWID().
I use SQL WHILE loop control statement to select n items from related category.
All these selected items are stored in a temporary table variable @t
This method enables repeating characters or multiple use of the same character in the resultant generated password.
And the last statement of the shared SQL script sort characters randomly and concatenates them into the string variable @pwd. You can refer to SQL Server tutorial for more on SQL string concatenation using For XML Path().
Here is the source codes of the SQL Server stored procedure which TSQL programmers can use to create random passwords.
create procedure Generate_Password (@pwd nvarchar(100) OUTPUT)
as
set nocount on
declare @len smallint -- password length
declare @len_min smallint -- minimum length for valid password
declare @len_max smallint -- maximum length for valid password
set @len_min = 8
set @len_max = 14
select @len = RAND( CAST(NEWID() AS VARBINARY) ) * (1 + @len_max - @len_min)
select @len = @len + @len_min
declare @U_cnt smallint
declare @L_cnt smallint
declare @N_cnt smallint
declare @S_cnt smallint
select @U_cnt = 1, @L_cnt = 1, @N_cnt = 1, @S_cnt = 1
select @U_cnt = RAND( CAST(NEWID() AS VARBINARY) ) * (@len - (@L_cnt + @N_cnt + @S_cnt)) + 1
select @L_cnt = RAND( CAST(NEWID() AS VARBINARY) ) * (@len - (@U_cnt + @N_cnt + @S_cnt)) + 1
select @N_cnt = RAND( CAST(NEWID() AS VARBINARY) ) * (@len - (@U_cnt + @L_cnt + @S_cnt)) + 1
select @S_cnt = @len - (@U_cnt + @L_cnt + @N_cnt)
--select @len, (@U_cnt + @L_cnt + @N_cnt + @S_cnt), @U_cnt, @L_cnt, @N_cnt, @S_cnt
declare @t as table (item char(1))
while @U_cnt > 0
begin
insert into @t(item) select top 1 item from PasswordCharacter where uppercase = 1 ORDER BY NEWID()
set @U_cnt = @U_cnt - 1
end
while @L_cnt > 0
begin
insert into @t(item) select top 1 item from PasswordCharacter where lowercase = 1 ORDER BY NEWID()
set @L_cnt = @L_cnt - 1
end
while @N_cnt > 0
begin
insert into @t(item) select top 1 item from PasswordCharacter where [numeric] = 1 ORDER BY NEWID()
set @N_cnt = @N_cnt - 1
end
while @S_cnt > 0
begin
insert into @t(item) select top 1 item from PasswordCharacter where special = 1 ORDER BY NEWID()
set @S_cnt = @S_cnt - 1
end
;with pwd as (
select top (@len) item from @t order by newid()
)
SELECT @pwd =
STUFF(
(
SELECT
item
FROM pwd
ORDER BY NEWID()
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'
), 1, 0, ''
)
if exists(
select top 1 * from CommonPasswordsList where [password] = @pwd
)
execute Generate_Password @pwd output
set nocount off
I also added a section at the bottom of the SQL stored procedure requiring existence of database table CommonPasswordsList which is used to check whether the generated password is in the list of top 10000 passwords most common among users. You can refer to my article Avoid Top 10000 Passwords for Secure Password for download and samples from the top 100 common passwords list.
After you run above Create Procedure script for SQL codes to create random passwords, below SQL programmers will see that I execute stored procedure Generate_Password for 5 times repeatedly using a SQL WHILE LOOP and display the automatically created password and its length
declare @password varchar(25)
declare @i int = 5
while @i > 0
begin
execute Generate_Password @password OUTPUT
select @password [sql password], len(@password) [password length]
set @i = @i - 1
end
Create password in SQL Server applications using SQL password generator code
As you can see all passwords contain upper and lower case letters, numbers and special characters. At least 1 character from each category exists in the randomly created password using SQL.