SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

Generate Random Passwords using T-SQL

Use following script to generate random Passwords

DECLARE @id int,
@list varchar
SET @list = 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
select replace(SUBSTRING(CONVERT(varchar(255), NEWID()),8, 8),'-','') as RANDOM


Suppose you have a table tblUser with columns

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
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

  DECLARE @id int,
  @list varchar
SET @list = 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
SET @id = (SELECT max(id) FROM tblUser)

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


this trigger create random password.



Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...