I looked all over the web to find a script I could use to have a random password generated in my insert statements on any new users in one of my membership databases for a client.
There are three parts, the reason for that is a Function can not directly handle the RAND() function call. What I did was make a view that just called the function and then a function to make it simple call (you could always omit this and do direct selects on the view).
Please feel free to comment. Here they are below:
USE
databasename
GO
--- Start vwRand ---
SET quoted_identifier
ON
GO
SET ansi_nulls
ON
GO
IF EXISTS
(SELECT
*
FROM
sys.VIEWS
WHERE object_id
=
Object_id(N'[dbo].[vwRand]'))
DROP VIEW
[dbo].[vwrand]
GO
--created by Rick Toner on 03/16/2007
--updated by Rick Toner on 03/16/2007
CREATE VIEW
dbo.vwrand
AS
SELECT
Rand()
AS R
GO
SET quoted_identifier
off
GO
SET ansi_nulls
ON
GO
--Uncomment the below line if
you need to implement security
--GRANT SELECT ON [vwRand] TO
[UserAccountOrDataseRole]
--- End vwRand ---
USE
databasename
GO
--- Start fn_Rand ---
SET quoted_identifier
ON
GO
SET ansi_nulls
ON
GO
IF EXISTS
(SELECT
*
FROM
dbo.sysobjects
WHERE id
=
Object_id(N'[dbo].[fn_Rand]')
AND xtype
IN (N'FN',N'IF',N'TF'))
DROP FUNCTION
[dbo].[fn_rand]
GO
--created by Rick Toner on 03/16/2007
--updated by Rick Toner on 03/16/2007
CREATE FUNCTION
fn_rand(
)
RETURNS FLOAT
AS
BEGIN
RETURN
(SELECT
r
FROM
vwrand)
END
GO
SET quoted_identifier
off
GO
SET ansi_nulls
ON
GO
--Uncomment the below line if
you need to implement security
--GRANT EXECUTE ON [fn_Rand]
TO [UserAccountOrDataseRole]
--- End fn_Rand ---
USE
databasename
GO
--- Start fn_RandomPassword
---
SET quoted_identifier
ON
GO
SET ansi_nulls
ON
GO
IF EXISTS
(SELECT
*
FROM
dbo.sysobjects
WHERE id
=
Object_id(N'[dbo].[fn_RandomPassword]')
AND xtype
IN (N'FN',N'IF',N'TF'))
DROP FUNCTION
[dbo].[fn_randompassword]
GO
--created by Rick Toner on 03/16/2007
--updated by Rick Toner on 03/16/2007
CREATE FUNCTION
fn_randompassword
(@MinLength
SMALLINT
= 5,
@MaxLength
SMALLINT =
8)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@Password VARCHAR(30)
DECLARE
@Length SMALLINT
DECLARE
@Position SMALLINT
DECLARE
@Characters
VARCHAR(55)
DECLARE
@LetterPosition
INT
DECLARE
@Letter CHAR(1)
SET
@Characters
= 'aeubcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ23456789'
SET
@Password
= ''
-- determine
length
SET
@Length =
@MinLength
+
Round(dbo.Fn_rand()
* (@MaxLength
- @MinLength),0,0)
SET
@Position
= 1
WHILE
@Position
<= @Length
BEGIN
BEGIN
BEGIN
SET @LetterPosition
=
CONVERT(INT,Round((dbo.Fn_rand()
* (Len(@Characters)
- 1)),0,0),1)
+ 1
SET @Letter
=
Substring(@Characters,@LetterPosition,1)
SET @Password
= @Password
+ @Letter
END
END
SET
@Position
= @Position
+ 1
-- incriment counter
END
-- return password
RETURN
@password
END
GO
SET quoted_identifier
off
GO
SET ansi_nulls
ON
GO
--Uncomment the below line if
you need to implement security
--GRANT EXECUTE ON [fn_RandomPassword]
TO [UserAccountOrDataseRole]
--- End fn_RandomPassword ---