Generate Social Security Numbers in TSQL

ssn_sex_generator.sql

DROP TABLE IF EXISTS #SSN

CREATE TABLE #SSN (
    ID INT IDENTITY(1, 1) PRIMARY KEY
  , SSN VARCHAR(11) MASKED WITH (FUNCTION = 'partial(0, "XXX-XX-", 4)')
  , SEX BIT
  , BIRTH INT
)

GO

WITH [rows] (Id) AS 
(
  SELECT 1
  UNION ALL 
  SELECT [Id] + 1
  FROM rows
  WHERE [Id] < 10000
)
, [raw] (ssn) AS 
(
  SELECT right('00' + cast(abs(checksum(newid())) as varchar(150)), 9)
  FROM [rows]
)
, [cleaned] (ssn, sex, birth) AS
(
  SELECT 
    CASE 
      WHEN LEFT(ssn, 3) < '001' THEN '4' + RIGHT(ssn, 10)
      WHEN LEFT(ssn, 3) > '649' THEN '4' + RIGHT(ssn, 10)
      ELSE ssn
    END 
    , CASE WHEN CAST(ssn AS INT) % 2 = 0 THEN 1 ELSE 0 END
    , FLOOR(RAND(CHECKSUM(CAST(ssn AS INT))) * (2010 - 1973 + 1) + 1973)
  FROM [raw]
)
, [formatted] (ssn, sex, birth) AS
(
  SELECT LEFT(ssn, 3) 
    + '-' + SUBSTRING(ssn, 4, 2) 
    + '-' + RIGHT(ssn, 4) 
    , sex, birth
  FROM [cleaned]
)
INSERT INTO #SSN (SSN, SEX, BIRTH)
SELECT SSN, SEX, BIRTH
FROM [formatted]
OPTION (MAXRECURSION 0);

CREATE USER MaskingTestUser WITHOUT LOGIN;  
GRANT SELECT TO MaskingTestUser;  
EXECUTE AS USER = 'MaskingTestUser';  
SELECT * FROM #SSN;  
REVERT;
DROP USER MaskingTestUser;  

SELECT * FROM #SSN;

Looks like.