Welcome to

SqlServer-QA.net

Sign in | Join | Help

Filling random passwords - Set based approach

In this post, I showed a way to generate 8 characters random password

 

If you have table with data and add a column for password and want to fill that column with random passwords, you can use this approach. I have seen the usage of a function that generates random password, and use that function in the update statement.

The following updates the column with random password in a set based approach


declare @password varchar(max), @number as int

set @password=''

select @password=@password+char(n) from
(
        select top 10000 t1.number as n from master..spt_values t1 cross join master..spt_values as t2 
        where t1.type='p' and t1.number between 48 and 122
        order by newid()
) as t

set @number=0

declare @t table(i int, pwd varchar(8))

insert into @t(i)
select top 100 number from master..spt_values
where type='p' and number>0

select * from @t

update @t
set pwd=substring(@password,@number*8,8),@number=@number+1

select * from @t

Published Monday, March 30, 2009 12:23 PM by Madhivanan

Comments

# Filling random passwords - Set based approach

In this post , I showed a way to generate 8 characters random password If you have table with data and

Monday, March 30, 2009 7:47 AM by Other SQL Server Blogs around the Web

# SQL Server Transact-SQL (SSQA.net) : Filling random passwords - Set based approach

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2009/03/30/5524.aspx

Monday, March 30, 2009 8:22 AM by SQL Server Transact-SQL (SSQA.net) : Filling random passwords - Set based approach
Anonymous comments are disabled