Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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



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,
  @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


END


this trigger create random password.


     

Comments

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

Loading comments...