Password Generator
For some reason, I've always had a hard time being creative on the spot, especially when needing to create a hardened password. It's kinda like going up to a comedian and asking them to say something funny.
Sure, I could bounce my stress-relief ball on the keyboard a few times to get something of a random set of characters, but here's my coded example.
CREATE proc sp__PassGen
@Length int, @pass varchar(15) output
AS
set nocount on
declare @CapBeg int,
@SmallBeg int,
@i int,
@cnt int,
@type int,
@bit char(1)
set @CapBeg = 65
set @SmallBeg = 97
Set @cnt = 26
declare @t Table (
RowNum int identity(1,1),
Letter char(1)
)
Set @i=1
While @i<=@cnt Begin
Insert Into @t (Letter) Values (char(@CapBeg))
Set @i=@i+1
Set @CapBeg = @CapBeg + 1
End
Set @i=1
While @i<=@cnt Begin
Insert Into @t (Letter) Values (char(@SmallBeg))
Set @i=@i+1
Set @SmallBeg = @SmallBeg + 1
End
set @i = 1
while @i <= @Length begin
if @i = 1
select @bit = letter from @t where RowNum = convert(int, rand()*52)
else begin
set @type = rand()*2
if @type = 1
select @bit = letter from @t where RowNum = convert(int, rand()*52)
else
select @bit = convert(varchar, convert(int, rand()*9+1))
end
if @i = 1
select @pass = @bit
else
select @pass = @pass + @bit
set @i = @i + 1
end
go
Without question, there's some kludginess that could be cleaned up. I've used this extensively in environments where SQL authenticated logins have to have their passwords changed routinely (and then have written carefully-worded emails to the Powers That Be as to why we can't rely more on Windows users or groups) or when replacing entire servers. Here's the server password changeout:
declare @p varchar(15)
declare @login varchar(128)
set @login = (select min(name) from syslogins where name <> 'sa' and isntname = 0)
while @login is not null begin
exec sp__PassGen 6, @pass = @p output
print @login+','+@p+','+@@servername
exec sp_password null, @p, @login
set @login = (select min(name) from syslogins where name <> 'sa' and isntname = 0 and name > @login)
end