-- This script prepares a table called dbo.Auth_Basic containing 10k username and password combinations.
-- 20% of the passwords, distributed randomly, are the top 10 most common passwords. 
-- First names: http://www.nrscotland.gov.uk/files/statistics/popular-forenames/2015/babiesnames-15-tab5-all-first-names-rank-order.csv 
-- Last names: http://www.quietaffiliate.com/Files/CSV_Database_of_Last_Names.csv 
-- Common passwords: https://github.com/danielmiessler/SecLists/blob/master/Passwords/10k_most_common.txt  

USE DBA 
SET NOCOUNT ON 
GO 

IF EXISTS ( SELECT name FROM sys.tables WHERE name = 'temp_firstNames' ) 
	DROP TABLE dbo.temp_firstNames 
CREATE TABLE dbo.temp_firstNames ( 
	firstName VARCHAR(20) ) 

BULK INSERT dbo.temp_firstNames 
FROM 'C:\temp\firstNames.csv' 
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a' ) 

IF EXISTS ( SELECT name FROM sys.tables WHERE name = 'temp_lastNames' ) 
	DROP TABLE dbo.temp_lastNames 
CREATE TABLE dbo.temp_lastNames ( 
	lastName VARCHAR(20) ) 

BULK INSERT dbo.temp_lastNames 
FROM 'C:\temp\lastNames.csv' 
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a' ) 

ALTER TABLE dbo.temp_firstNames 
ADD fnID INT IDENTITY(1,1) NOT NULL 

ALTER TABLE dbo.temp_lastNames 
ADD lnID INT IDENTITY(1,1) NOT NULL

ALTER TABLE dbo.temp_firstNames REBUILD 
ALTER TABLE dbo.temp_lastNames REBUILD 

IF EXISTS ( SELECT name FROM sys.tables WHERE name = 'Auth_Basic' ) 
	DROP TABLE dbo.Auth_Basic 
CREATE TABLE dbo.Auth_Basic ( 
	UserID INT IDENTITY(10000,1), 
	UserName VARCHAR(50), 
	Passwd VARCHAR(50) ) 
GO

-- generate 10k random usernames based on real first/last name pairs and a uniquifier
DECLARE @maxFN INT = ( SELECT MAX(fnID) FROM dbo.temp_firstNames ) 
DECLARE @maxLN INT = ( SELECT MAX(lnID) FROM dbo.temp_lastNames ) 
DECLARE @nameString VARCHAR(50) 
DECLARE @fnID INT, @lnID INT 
DECLARE @cnt INT = 0

WHILE @cnt <= 10000
BEGIN
	SET @fnID = ABS(CHECKSUM(NEWID())) % @maxFN + 1 
	SET @lnID = ABS(CHECKSUM(NEWID())) % @maxLN + 1 
	SET @nameString = ( SELECT firstName FROM dbo.temp_firstNames WHERE fnID = @fnID ) 
	SET @nameString = @nameString + ( SELECT lastName FROM dbo.temp_lastNames WHERE lnID = @lnID ) 
	SET @nameString = REPLACE(REPLACE(REPLACE(@nameString,CHAR(13),''),CHAR(10),''),' ','') + CAST(ABS(CHECKSUM(NEWID())) % 1000 AS VARCHAR(4)) 
	INSERT INTO dbo.Auth_Basic (UserName) 
		SELECT	@nameString 
		SET @cnt += 1 
END 
GO

-- set passwords.  Use top 10 most common passwords for 20% of all names and random passwords for remainder
-- need to randomise username list first to make it realistic
ALTER TABLE dbo.Auth_Basic 
ADD NewUserID INT 
GO 

UPDATE dbo.Auth_Basic 
SET NewUserID = 0 

ALTER TABLE dbo.Auth_Basic 
ALTER COLUMN NewUserID INT NOT NULL 
GO 

UPDATE	dbo.Auth_Basic 
SET		NewUserID = ( SELECT ABS(CHECKSUM(NEWID())) ) 

ALTER TABLE dbo.Auth_Basic 
DROP COLUMN UserID 

ALTER TABLE dbo.Auth_Basic 
ADD CONSTRAINT pk_NewUserID_Auth_Basic PRIMARY KEY (NewUserID) 
GO

IF EXISTS ( SELECT name FROM sys.tables WHERE name = 'temp_mostCommonPasswords' ) 
	DROP TABLE dbo.temp_mostCommonPasswords
CREATE TABLE dbo.temp_mostCommonPasswords ( 
	pid		INT IDENTITY(1,1) NOT NULL, 
	passwd	VARCHAR(20) ) 

INSERT INTO dbo.temp_mostCommonPasswords (passwd)
	SELECT 'password' UNION SELECT '123456' UNION SELECT '12345678' UNION SELECT '1234' 
	UNION SELECT 'qwerty' UNION SELECT '12345' UNION SELECT 'dragon' UNION SELECT 'letmein' 
	UNION SELECT 'admin' UNION SELECT 'abc123' 

-- update 2k random rows in our 10k table with a randomly-selected top 10 most common password from the list
DECLARE @pcnt INT = 0, @rid INT 
WHILE @pcnt <= 2000 
BEGIN
	SET		@rid = ABS(CHECKSUM(NEWID())) % 10 + 1
	;WITH reordered AS ( SELECT ROW_NUMBER() OVER ( ORDER BY NewUserID ) [row], passwd, NewUserID FROM dbo.Auth_Basic )
	UPDATE	a
	SET		passwd = ( SELECT passwd FROM dbo.temp_mostCommonPasswords WHERE pid = @rid  ) 
	FROM	dbo.Auth_Basic a INNER JOIN reordered r ON a.NewUserID = r.NewUserID 
	AND		r.[row] = @pcnt 
	SET		@pcnt += 1
END 
GO

-- update all other passwords to be random 8-character strings in range a-z 
UPDATE	dbo.Auth_Basic 
SET		passwd = 
		CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
		CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
		CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
		CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) 
WHERE	passwd IS NULL 
GO 

ALTER TABLE dbo.Auth_Basic 
DROP CONSTRAINT pk_NewUserID_Auth_Basic 

ALTER TABLE dbo.Auth_Basic 
DROP COLUMN NewUserID 

-- some trickery here to reorder randomly and assign sequential UserIDs
ALTER TABLE dbo.Auth_Basic 
ALTER COLUMN UserName VARCHAR(50) NOT NULL 

ALTER TABLE dbo.Auth_Basic 
ADD CONSTRAINT pk_tmp PRIMARY KEY (UserName) 
GO

ALTER TABLE dbo.Auth_Basic 
ADD UserID INT IDENTITY(10000,1) 

ALTER TABLE dbo.Auth_Basic 
DROP CONSTRAINT pk_tmp 
GO

ALTER TABLE dbo.Auth_Basic 
ADD CONSTRAINT pk_UserID_Auth_Basic PRIMARY KEY (UserID) 


DROP TABLE dbo.temp_firstNames 
DROP TABLE dbo.temp_lastNames 
DROP TABLE dbo.temp_mostCommonPasswords 
GO

SELECT * FROM dbo.Auth_Basic