| To create a randome password i have created 2 function and one view to get complex password below is the code for the same
(1)First part is view
CREATE VIEW Get_RAND
AS
SELECT RAND() AS MyRAND
GO
(2)Second part is to get random number in any range define in it
CREATE FUNCTION RANDBETWEEN(@LowerBound INT, @UpperBound INT)
RETURNS INT
AS
BEGIN
DECLARE @TMP FLOAT;
SELECT @TMP = (SELECT MyRAND FROM Get_RAND);
RETURN CAST(@TMP* (@UpperBound - @LowerBound) + @LowerBound AS INT);
END
GO
(3)Third is to create a 10 digit password with different keywords and syntax
alter FUNCTION dbo.GeneratePassword ()
RETURNS varchar(10)
AS
BEGIN
DECLARE @randInt int;
DECLARE @NewCharacter varchar(1);
DECLARE @NewPassword varchar(10);
SET @NewPassword='';
--6 random characters
WHILE (LEN(@NewPassword) <6)
BEGIN
select @randInt=dbo.RANDBETWEEN(48,122)
IF @randInt<=57 OR (@randInt>=60 AND @randInt<=93) OR (@randInt>=97 AND @randInt<=122)
Begin
select @NewCharacter=CHAR(@randInt)
select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
END
END
--Ensure a lowercase
select @NewCharacter=CHAR(dbo.randbetween(97,122))
select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
--Ensure an upper case
select @NewCharacter=CHAR(dbo.randbetween(65,90))
select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
--Ensure a number
select @NewCharacter=CHAR(dbo.randbetween(48,57))
select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
--Ensure a symbol
WHILE (LEN(@NewPassword) <10)
BEGIN
select @randInt=dbo.randbetween(33,64)
-- ! # $ % & < = > ? @
IF @randInt=33 OR (@randInt>=35 AND @randInt<=38) OR (@randInt>=60 AND @randInt<=64)
Begin
select @NewCharacter=CHAR(@randInt)
select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
END
END
RETURN(@NewPassword);
END;
GO
And to creata password after executing the above code we need only to call
select GeneratePassword() | | |