To generate a complex password, we should not only get random value from the set of the characters but also guarantee the combination of upper case, lower case, numbers and special characters. In the procedure below, the password generated will include all 4 parts.

if object_id('GeneratePassword') is not null
 drop procedure GeneratePassword
go
create procedure GeneratePassword(@length int)
as
begin
 set nocount on
 if not (@length between 4 and 128)
 begin
  raiserror('The length of password must be between %d and %d.', 16, 1, 4, 128)
  return
 end
 declare @s1 varchar(128),@s2 varchar(128), @s3 varchar(128), @s4 varchar(128), @sql nvarchar(4000)
 declare @i int,  @password varchar(128), @r int
 select @s1 = '0123456789',
  @s2 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  @s3 = 'abcdefghijklmnopqrstuvwxyz',
  @s4 = '!#$%&()*+-./:<=>?@[\]^_`{|}~' --,; are excluded
 declare @password_table table (c char(1), k float primary key default(rand()))--, o int identity(1,1) )
 select @i = 0,@password = '';
 while (1=1) 
 begin
  select @r = 0
  while @r < 1 select @r = cast(rand()*len(@s1) as int)
  insert into @password_table(c) values(substring(@s1, @r , 1))
  select @i = @i + 1
  if @i >= @length break
 
  select @r = 0
  while @r < 1 select @r = cast(rand()*len(@s1) as int)
  insert into @password_table(c) values(substring(@s2, @r, 1))
  select @i = @i + 1
  if @i >= @length break
 
  select @r = 0
  while @r < 1 select @r = cast(rand()*len(@s1) as int)
  insert into @password_table(c) values(substring(@s3, @r, 1))
  select @i = @i + 1
  if @i >= @length break
 
  select @r = 0
  while @r < 1 select @r = cast(rand()*len(@s1) as int)
  insert into @password_table(c) values(substring(@s4, @r, 1))
  select @i = @i + 1
  if @i >= @length break 
 end
 select @password = @password + c from @password_table
 select @sql = '
    declare @password varchar('+cast(@length as varchar(20))+')
    select @password = @pwd
    select @password
   '
 exec sp_executesql @sql, N'@pwd varchar(128)', @pwd = @password
end
go
grant exec on GeneratePassword to public
go
--test
exec GeneratePassword 20
-------------------- 
2I!&2d8FA&cb5aAHd!)8

Generating Random Password by a T-SQL Procedure

You May Also Like

One thought on “Generating Random Password by a T-SQL Procedure

Leave a Reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.