I use this
DECLARE @__numberOfUpperCaseCharacters int
DECLARE @__numberOfLowerCaseCharacters int
DECLARE @__numberOfNumericCharacters int
DECLARE @__numberOfSpecialCharacters int
SET @__numberOfUpperCaseCharacters = 1
SET @__numberOfLowerCaseCharacters = 5
SET @__numberOfNumericCharacters = 1
SET @__numberOfSpecialCharacters = 1
;WITH CTE_UpperCaseCharacters AS
(
SELECT
CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomUpperCaseCharacter
,1 AS cnt
WHERE @__numberOfUpperCaseCharacters > 0
UNION ALL
SELECT
CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomUpperCaseCharacter
,CTE_UpperCaseCharacters.cnt + 1 AS cnt
FROM CTE_UpperCaseCharacters
WHERE CTE_UpperCaseCharacters.cnt < @__numberOfUpperCaseCharacters
)
,CTE_LowerCaseCharacters AS
(
SELECT
CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomLowerCaseCharacter
,1 AS cnt
WHERE @__numberOfLowerCaseCharacters > 0
UNION ALL
SELECT
CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomLowerCaseCharacter
,CTE_LowerCaseCharacters.cnt + 1 AS cnt
FROM CTE_LowerCaseCharacters
WHERE CTE_LowerCaseCharacters.cnt < @__numberOfLowerCaseCharacters
)
,CTE_Numbers AS
(
SELECT
CAST
(
(
ABS
(
CHECKSUM(NEWID())
)
%10
)
AS varchar(1)
) AS RandomInteger
,1 AS cnt
WHERE @__numberOfNumericCharacters > 0
UNION ALL
SELECT
CAST
(
(
ABS
(
CHECKSUM(NEWID())
)
%10
)
AS varchar(1)
) AS RandomInteger
,CTE_Numbers.cnt + 1 AS cnt
FROM CTE_Numbers
WHERE CTE_Numbers.cnt < @__numberOfNumericCharacters
)
,CTE_SpecialCharacters AS
(
SELECT
CHAR(ASCII('!')+(ABS(CHECKSUM(NEWID()))%15)) AS RandomSpecialCharacter
,1 AS cnt
WHERE @__numberOfSpecialCharacters > 0
UNION ALL
SELECT
CHAR(ASCII('!')+(ABS(CHECKSUM(NEWID()))%15)) AS RandomSpecialCharacter
,CTE_SpecialCharacters.cnt + 1 AS cnt
FROM CTE_SpecialCharacters
WHERE CTE_SpecialCharacters.cnt < @__numberOfSpecialCharacters
)
,CTE AS
(
SELECT RandomSpecialCharacter AS RandomCharacter, cnt
FROM CTE_SpecialCharacters
UNION ALL
SELECT RandomUpperCaseCharacter AS RandomCharacter, cnt
FROM CTE_UpperCaseCharacters
UNION ALL
SELECT RandomLowerCaseCharacter AS RandomCharacter, cnt
FROM CTE_LowerCaseCharacters
UNION ALL
SELECT RandomInteger AS RandomCharacter, cnt
FROM CTE_Numbers
)
SELECT
(
SELECT RandomCharacter AS [text()]
FROM CTE
ORDER BY NEWID()
FOR XML PATH(''), TYPE
).value('.', 'varchar(50)') AS foo
-- OPTION (MAXRECURSION 0)
But because SQL-Server is so wonderful, you can't use NEWID in a scalar function, so I create an additional view "dbo.random". As below:
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Random]'))
EXECUTE('CREATE VIEW dbo.Random AS SELECT NEWID() AS [RUID]');
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_GenerateRandomPassword]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_GenerateRandomPassword]
GO
-- ========================================================================
-- Author: ME
-- Create date: 29.11.2016
-- Last modified: 29.11.2016
-- Description: Erstellen eines zufälligen Passworts
-- ========================================================================
-- PRE: Anzahl Buchstaben in spezifizierter Kombination
-- POST: Generiere Liste mit jeweiliger Anzahl Buchstaben.
-- Gibt string(shuffle(array(lower + upper + numeric + special))) zurück
-- PRINT dbo.fu_GenerateRandomPassword ( 31, 12, 2010)
CREATE FUNCTION [dbo].[fu_GenerateRandomPassword](@__numberOfLowerCaseCharacters as int, @__numberOfUpperCaseCharacters as int, @__numberOfNumericCharacters as int, @__numberOfSpecialCharacters as int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @__retValue varchar(50);
--DECLARE @__numberOfLowerCaseCharacters int;
--SET @__numberOfLowerCaseCharacters = 5;
--DECLARE @__numberOfUpperCaseCharacters int;
--SET @__numberOfUpperCaseCharacters = 1;
--DECLARE @__numberOfNumericCharacters int;
--SET @__numberOfNumericCharacters = 1;
--DECLARE @__numberOfSpecialCharacters int;
--SET @__numberOfSpecialCharacters = 1;
WITH CTE_UpperCaseCharacters AS
(
SELECT
CHAR(ASCII('A')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomUpperCaseCharacter
,1 AS cnt
WHERE @__numberOfUpperCaseCharacters > 0
UNION ALL
SELECT
CHAR(ASCII('A')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomUpperCaseCharacter
,CTE_UpperCaseCharacters.cnt + 1 AS cnt
FROM CTE_UpperCaseCharacters
WHERE CTE_UpperCaseCharacters.cnt < @__numberOfUpperCaseCharacters
)
,CTE_LowerCaseCharacters AS
(
SELECT
CHAR(ASCII('a')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomLowerCaseCharacter
,1 AS cnt
WHERE @__numberOfLowerCaseCharacters > 0
UNION ALL
SELECT
CHAR(ASCII('a')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomLowerCaseCharacter
,CTE_LowerCaseCharacters.cnt + 1 AS cnt
FROM CTE_LowerCaseCharacters
WHERE CTE_LowerCaseCharacters.cnt < @__numberOfLowerCaseCharacters
)
,CTE_Numbers AS
(
SELECT
CAST
(
(
ABS
(
CHECKSUM((SELECT RUID FROM Random))
)
%10
)
AS varchar(1)
) AS RandomInteger
,1 AS cnt
WHERE @__numberOfNumericCharacters > 0
UNION ALL
SELECT
CAST
(
(
ABS
(
CHECKSUM((SELECT RUID FROM Random))
)
%10
)
AS varchar(1)
) AS RandomInteger
,CTE_Numbers.cnt + 1 AS cnt
FROM CTE_Numbers
WHERE CTE_Numbers.cnt < @__numberOfNumericCharacters
)
,CTE_SpecialCharacters AS
(
SELECT
CHAR(ASCII('!')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%15)) AS RandomSpecialCharacter
,1 AS cnt
WHERE @__numberOfSpecialCharacters > 0
UNION ALL
SELECT
CHAR(ASCII('!')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%15)) AS RandomSpecialCharacter
,CTE_SpecialCharacters.cnt + 1 AS cnt
FROM CTE_SpecialCharacters
WHERE CTE_SpecialCharacters.cnt < @__numberOfSpecialCharacters
)
,CTE AS
(
SELECT RandomSpecialCharacter AS RandomCharacter, cnt
FROM CTE_SpecialCharacters
UNION ALL
SELECT RandomUpperCaseCharacter AS RandomCharacter, cnt
FROM CTE_UpperCaseCharacters
UNION ALL
SELECT RandomLowerCaseCharacter AS RandomCharacter, cnt
FROM CTE_LowerCaseCharacters
UNION ALL
SELECT RandomInteger AS RandomCharacter, cnt
FROM CTE_Numbers
)
SELECT
@__retValue = (
SELECT RandomCharacter AS [text()]
FROM CTE
ORDER BY (SELECT RUID FROM Random)
FOR XML PATH(''), TYPE
).value('.', 'varchar(50)')
-- OPTION (MAXRECURSION 0)
;
RETURN @__retValue;
END
GO
CREATE
script for Roles in SQL 2014. May be an interesting starting point for you.