Tuesday 12 February 2013

Generate Random Passwords using T-SQL



Use following script to generate random Passwords


select replace(SUBSTRING(CONVERT(varchar(255), NEWID()),8, 8),'-','') as RANDOM



Example:

Suppose you have a table tblUser with columns
[id],[userid],[password].

CREATE TABLE [dbo].[tblUser](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [userid] [varchar](250) NULL,
      [password] [varchar](350) NULL,
      )


Now Create a trigger on table tblUser and update password column with random password

CREATE TRIGGER [dbo].[insUser]
   ON  [dbo].[tblUser]
   AFTER Insert
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

  DECLARE @id int
  SET @id = (SELECT max(id) FROM tblUser)

UPDATE tblUser set password =replace(SUBSTRING(CONVERT(varchar(255), NEWID()),8, 8),'-','') WHERE id=@id


END


this trigger create random password.



You can also create a function and can use any where.
To create function to generate random password, first you need to create   a view as


CREATE  VIEW getNewID as SELECT newid() as new_id

Because NEWID() does not work in function.Error occurred as


Msg 443, Level 16, State 1, Procedure fn_random, Line 7
Invalid use of a side-effecting operator 'newid' within a function.








After creating view,You create function like


CREATE function fn_random()
RETURNS varchar(16)
with encryption
begin
declare @pass varchar(16)
declare @n varchar(max)
select @n=new_id from getNewID
Select @pass=replace(SUBSTRING(CONVERT(varchar(255),CAST(@n as varchar(max)) ),8, 8),'-','')
return @pass
end



     
Now you can query function like


select dbo.fn_random()
Output:
A56B53349

again execute the same






select dbo.fn_random()
Output:
0E87A21C4

7 comments:

  1. Hеllo there, I do think your blog could be havіng web browser compatibility pгoblems.
    Whеnever I look at your wеbsite іn Safaгi, it looks fine hοwever, if
    opening in Іntеrnеt Eхplorer, it has some overlаpρing іssueѕ.
    I merely ωanted to give you a quick hеads uρ!
    Оther than that, fantastiс blоg!

    Also visit my weblog - rv rentals in ohio motor home

    ReplyDelete
    Replies
    1. GREAT ARTICLE!, DUDE I NEED ONLY NUMERIC VALUE

      LIKE
      993223
      552343
      233434

      HOW CAN I ACHIEVE THAT?

      Delete
    2. you can use

      declare @number int
      set @number = (select cast((rand()*1000000) as decimal(6)))
      if len(@number) = 6 --This condition is due to it returns sometimes 5 digidts output.
      select @number
      else
      set @number = convert(varchar,(select cast((rand()*10) as decimal(1))))+convert(varchar,@number)

      Delete
  2. Thnx tat was vey useful............
    Can u tel u how to update a table with the value returned by that function?

    ReplyDelete
    Replies
    1. Hi Raja,
      You can use below query to update column of a table.

      update emp set password=(select dbo.fn_random()) where ID=3

      Delete
  3. Thnx tat was very useful.........
    Can u tell me how to update a table with the value returned by that function?

    ReplyDelete
  4. Although this method creates values formed of numeric and characters in general a password has to include special characters too. Thank you for the SQL trick you shared with us. An alternative method is shared at here SQL password generator to create random passwords for the SQL developers to generate random passwords using SQL with special characters.

    ReplyDelete

Please leave a comment for this post