Dec 192011
 

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

  One Response to “Generating Random Password by a T-SQL Procedure”

  1. Thanks a lot

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

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

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