• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1297
  • Last Modified:

T SQL Update column with Random Passwords

Hi Experts,

I have a table with a password column that I would like to update with new passwords. I found this query which works great but I can't seem to incorporate it with an update statement

declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
	select top 8 number  as n from master..spt_values 
	where type='p' and number between 48 and 122
	order by newid()
) as t
select @password

Open in new window


Any suggestions?

Cheers,
Numb
0
ComfortablyNumb
Asked:
ComfortablyNumb
  • 2
1 Solution
 
SharathData EngineerCommented:
Create a view like this.
create view vwRandom as select NEWID() New_ID

Open in new window

Then create a function like this.
create function dbo.fnPassword() returns varchar(20) as 
begin
declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
	select top 8 number  as n from master..spt_values 
	where type='p' and number between 48 and 122
	order by (select New_ID from vwRandom)
) as t
return @password
end

Open in new window

Now update the password column with this statement.
update your_table set Password = dbo.fnPassword()

Open in new window

Tested on some sample data.
declare @table table (UserID varchar(10),Password varchar(10))
insert @table values ('User1','password1'),('User2','password2'),('User3','password3')
update @table set Password = dbo.fnPassword()
select * from @table

Open in new window

0
 
SharathData EngineerCommented:
Here is the result on my sample test.
declare @table table (UserID varchar(10),Password varchar(10))
insert @table values ('User1','password1'),('User2','password2'),('User3','password3')
select * from @table
/*
UserID	Password
User1	password1
User2	password2
User3	password3
*/
update @table set Password = dbo.fnPassword()
select * from @table
/*
UserID	Password
User1	Ipt^S72m
User2	s0dgT_RE
User3	z]w4EeYO
*/

Open in new window

0
 
LowfatspreadCommented:
like this....

where pk is the primary key column of your "password" table....
update yourtable
 set password = (select [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]
            From (select  
                 char(number) as n 
                 ,ROW_NUMBER() over (order by newid()) as rn
                 from master..spt_values 
                 cross join yourtable as x
	      where type='p' and number between 48 and 122 
	      and x.pk=yourtable.pk) as p
	      pivot (max(n) for rn in ([1],[2],[3],[4],[5],[6],[7],[8])) as pvt
	)

Open in new window

0
 
ComfortablyNumbAuthor Commented:
Fantastic! Did the job perfectly. Thank you.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now