/**
use master
SELECT name, dbo.fn_RandomPassword(8,8) as UserPassword
FROM sysusers
dbo.fn_RandomPassword(最小长度,最大长度)
**/
--- Start vwRand ---
SET quoted_identifier ON
GO
SET ansi_nulls ON
GO
IF EXISTS (SELECT *
FROM sys.sysobjects
WHERE object_id = Object_id(N'[dbo].[vwRand]') and OBJECTPROPERTY(id, N'IsView') = 1)
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 ---
--- 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 ---
--- 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 ---
use master
SELECT name, dbo.fn_RandomPassword(8,8) as UserPassword
FROM sysusers
dbo.fn_RandomPassword(最小长度,最大长度)
**/
--- Start vwRand ---
SET quoted_identifier ON
GO
SET ansi_nulls ON
GO
IF EXISTS (SELECT *
FROM sys.sysobjects
WHERE object_id = Object_id(N'[dbo].[vwRand]') and OBJECTPROPERTY(id, N'IsView') = 1)
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 ---
--- 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 ---
--- 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 ---
转载于:https://blog.51cto.com/cto99/279096