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