text: ﻿IF OBJECT_ID(N'[dbo].[sp_SQLPasswordAudit]') IS NULL
    EXEC (N'CREATE PROCEDURE [dbo].[sp_SQLPasswordAudit] AS RETURN 0;');
  GO
  
  
  ALTER PROCEDURE [dbo].[sp_SQLPasswordAudit]
  (
  	@Help			BIT				= 0,
  	@ExcludeDisabled	BIT				= 1,
  	@IgnorePolicy		BIT				= 0,
  	@ResultsToTable		BIT				= 0,
  	@SourceLists		NVARCHAR(500)			= N'ALL',
  	@UseInstanceInfo	BIT				= 0,
  	@CustomTerm		NVARCHAR(32)			= N''
  )
  AS
  SET NOCOUNT ON;
  DECLARE		@Delim		NVARCHAR(1) 			= N',';
  DECLARE		@IsDisabled	BIT				= 0;
  
  IF (@Help = 1)
  BEGIN
  PRINT '
  	SQLPasswordAudit from https://github.com/TheVladdestVlad/SQLPasswordAudit
  	This stored procedure checks the passwords of your existing SQL Logins against
  	popular password lists.
  	Experimental: The stored procedure can also generate custom instance-specific 
  	passwords using information like:
  		- database names and creation dates
  		- login names and creation dates
  		- instance name and creation date
  		- common terms found in passwords
  		- symbols
  		- custom term (company name, project name, etc.) provided by the user
  		These are combined using common password paterns.
  		To learn more, visit https://github.com/TheVladdestVlad/SQLPasswordAudit, where
  	you can download updated versions of the script and of the password lists, as well as 
  	find detailed usage information and contribute your own code and suggestions.
  
  	Limitations:
  	This has only been developed for and tested on SQL Server 2012 through 2019.
  	In the cases of logins migrated from older instances, the creation date would 
  	no longer help, since it no longer matches the original creation date of the 
  	login(s) on the source instance.
  
  	Heads-up:
  	Running PWDCOMPARE, the function that this script calls to perform  
  	the comparison between the clear text password candidates and the password_hash column 
  	of sys.sql_login, against large password lsits will cause PREEMPTIVE_OS_CRYPTOPS waits 
  	to increase.
  	https://www.sqlskills.com/help/waits/preemptive_os_cryptops/
  	If you execute the stored procedure with option to generate passwords based on instance 
  	information and custom term on an instance that has many (20+) databases and logins, 
  	the quasi-temporary table [dbo].[TempCustomPasswords] created during execution will 
  	end up storing a lot of data. Be prepared and pre-grow the data file by a few GB.
  	It is higly recommended to have this stored procedure and the [dbo].[Passwords] table
  	in a non-system databases that is dedicated to DBA-related tasks and can be set to use 
  	the SIMPLE recovery model.
  
  	 Dependencies:
  	 If you wish to run the stored procedure against password lists,the [dbo].[Passwords] 
  	 table needs to be created in the same database as the stored procedure, and it also 
  	 has to be populated with the password list(s) you intend to use.
  	 The table and various password list scripts designed for this store procedure
  	 can be found here -> https://github.com/TheVladdestVlad/SQLPasswordAudit/tree/master/PasswordLists
  
  	 Parameter explanations:
  		@ExcludeDisabled = Used to specify whether logins marked as disabled should be skipped or not. 
  							Default is 1 (yes)
  		@IgnorePolicy    = Specifies whether or not passwords shorter than 8 characters should be checked 
  							against hashes of logins where is_policy_checked is set to 1.
  							Default is 0 (check passwords < 8 characters only against 
  							logins having is_policy_checked= 0).
  		@ResultsToTable	 =  Specifies whether or not the results of the check should be saved in a permanent table 
  							(the table is created by the stored procedure). Default is 0 (No)
  		@SourceLists	 = Name(s) of the source lists, comma separated, that should be checked against 
  						(e.g. rockyou, nansh0u, etc.), based on the ones existing in the [dbo].[Passwords] table.
  						 If you plan on only using instance-specific auto-generated passwords, provide a name that 
  						 doesn''t exist (e.g nolist). Default is ALL.
  		@UseInstanceInfo = Specifies wheter or not the stored procedure should auto generate
  							instance-specific passwords and includem them in the check. Default is 0 (No)
  		@CustomTerm		 =	Used to pass a single custom term when the stored procedure should generat
  							instance-specific passwords.
  							This can be something like comapny name or the name of an internal project.
  							Default is '''' (an epmty string).
  
  	MIT License
  
  	Copyright (c) 2020 Vlad Drumea
  	
  	Permission is hereby granted, free of charge, to any person obtaining a copy
  	of this software and associated documentation files (the "Software"), to deal
  	in the Software without restriction, including without limitation the rights
  	to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  	copies of the Software, and to permit persons to whom the Software is
  	furnished to do so, subject to the following conditions:
  	
  	The above copyright notice and this permission notice shall be included in all
  	copies or substantial portions of the Software.
  	
  	THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  	IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  	FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  	AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  	LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  	OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
  	SOFTWARE.
  	'
  	RETURN
  END
  
    IF OBJECT_ID('tempdb..#SelectedLists') IS NOT NULL
  	BEGIN
  			DROP TABLE #SelectedLists;
  	END;
   CREATE TABLE #SelectedLists
  	(ListName nvarchar(60),
  	StartPosition int,
  	Selected bit);
  
  /* Handle @ExcludeDisabled */
  IF (@ExcludeDisabled = 0)
  BEGIN
  SET @IsDisabled = 1;
  END;
  
  /*
  	Creating a table to store custom passwords - will be dropped in the cleanup step
  */
  IF EXISTS(SELECT * FROM sys.objects 
  	WHERE [object_id] = OBJECT_ID(N'[dbo].[TempCustomPasswords]') AND [type] in (N'U'))
  	BEGIN
  		DROP TABLE [dbo].[TempCustomPasswords];
  	END;
  CREATE TABLE [dbo].[TempCustomPasswords]
  (
  [ID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
  [Pass] NVARCHAR(128),
  [SourceList] NVARCHAR(128)
  );
  
  /* Prep for instance-specific and/or custom term-based password generation */
  
  IF (@UseInstanceInfo=1 OR @CustomTerm <> N'')
  BEGIN
  	IF NOT EXISTS (SELECT * FROM sys.objects
  			WHERE [object_id] = OBJECT_ID(N'[dbo].[Passwords]') AND [type] in (N'U'))
  		BEGIN
  		CREATE TABLE [dbo].[Passwords]
  			(
  			[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
  			[Pass] [nvarchar](128) NULL,
  			[SourceList] [nvarchar](128) NULL
  			);
  		END;
  DECLARE @InstanceCreated VARCHAR(10);
  DECLARE @CurrentYear VARCHAR(4);
  DECLARE @InstanceName NVARCHAR(16);
  
  /* Set @InstanceName */
  SELECT @InstanceName =	CAST(
  							ISNULL(
  								SERVERPROPERTY('InstanceName'),
  								SERVERPROPERTY('ServerName')
  							) AS NVARCHAR(16)
  							);
  
  /* Set @CurrentYear */
  SELECT @CurrentYear = CAST(
  						CONVERT(VARCHAR,GETDATE(),23) AS VARCHAR(4)
  							);
  /* Set @InstanceCreated*/
  SELECT @InstanceCreated = CONVERT(VARCHAR,[create_date],23)
  FROM sys.server_principals
  WHERE sid = 0x010100000000000512000000;
  
  /*
  	Create the tamp table that will store instance-related data 
  	and common terms to be used for password combinations
  */
    IF OBJECT_ID('tempdb..#CustomPassCombo') IS NOT NULL
  	BEGIN
  			DROP TABLE #CustomPassCombo;
  	END;
  CREATE TABLE #CustomPassCombo
  ( 
  	[InstanceInfo]		NVARCHAR(52),
  	[CommonTerms]		NVARCHAR(14),
  	[DatabaseCreated]	VARCHAR(10),
  	[LoginCreated]		VARCHAR(10)
    )
  /* 
  	Declare @CommonTerms table variable to store terms commonly found in passwords 
  */
  DECLARE @CommonTerms TABLE 
  	(
  		[CommTerms]		NVARCHAR(20)
  	);
  
  /* 
  	Populate the @CommonTerms table variable 
  */
  INSERT INTO @CommonTerms
  ([CommTerms])
  VALUES
  (N'Owner'),(N'Admin'),(N'Adm'),(N'Administrator'),(N'Root'),(N'SA'),(N'Manage'),
  (N'ReadOnly'),(N'Read_Only'),(N'Read-Only'),(N'Read Only'),(N'R/O'),(N'R.O.'),
  (N'R.O'),(N'R.O.'),(N'R-O'),(N'R_O'),(N'RO'),(N'R O'),(N'ReadWrite'),(N'Read_Write'),
  (N'Read-Write'),(N'Read Write'),(N'R/W'),(N'R.W.'),(N'R.W'),(N'R-W'),(N'R_W'),
  (N'R W'),(N'RW'),(N'Write'),(N'SysAdmin'),(N'Sys_Admin'),(N'Sys-Admin'),(N'Sys Admin'),
  (N'Sys'),(N'System'),(N'IT'),(N'I.T.'),(N'DDB'),(N'Dev'),(N'Develop'),(N'Developer'),
  (N'Development'),(N'TDB'),(N'Test'),(N'TestDB'),(N'Tester'),(N'QA'),(N'QADB'),
  (N'Assurance'),(N'ADB'),(N'SB'),(N'SBDB'),(N'SandBox'),(N'Sand_Box'),(N'Sand-Box'),
  (N'Sand Box'),(N'Prod'),(N'Product'),(N'Production'),(N'Staging'),(N'Spring'),
  (N'Summer'),(N'Fall'),(N'Winter'),(N'Pass'),(N'PWD'),(N'Pword'),(N'Password'),('dbo'),
  (N'Pa$$w0rd'),(N'Pa55w0rd');
  
  /* 
  	Declare @Symbols table variable to store symbols commonly found in passwords 
  */
  DECLARE @Symbols TABLE 
  	(
  		[Sym] NVARCHAR(1)
  	);
  /* 
  	Populate the @Symbols table variable 
  */
  INSERT INTO @Symbols
  (Sym)
  VALUES
  (N'~'),(N'`'),(N'!'),(N'@'),(N'#'),(N'$'),(N'%'),(N'^'),(N'&'),(N'*'),(N'(N'),(N')'),(N'_'),
  (N'-'),(N'+'),(N'='),(N'{'),(N'}'),(N'['),(N']'),(N'|'),(N';'),(N':'),(N''''),
  (N'"'),(N'<'),(N'>'),(N','),(N'.'),(N'?'),(N'/'),(N'\'),(N''),(N'0'),(N'1'),(N'2'),(N'3'),
  (N'4'),(N'5'),(N'6'),(N'7'),(N'8'),(N'9');
  
  /* 
  	Populate #CustomPassCombo with user database names and their creation dates
  	cross applied with the values in the @CommonTerms table variable 
  */
  INSERT INTO #CustomPassCombo
  			(
  				[InstanceInfo], 
  				[CommonTerms], 
  				[DatabaseCreated]
  			)
  SELECT	
  	CAST([DB].[name] AS NVARCHAR(52))		AS [InstanceInfo], 
  	[CT].[CommTerms]						AS [CommonTerms], 
  	CONVERT(VARCHAR,[DB].[create_date],23)	AS [DatabaseCreated]
  FROM sys.databases AS [DB]
  CROSS APPLY @CommonTerms AS [CT] 
  	WHERE [DB].[database_id] > 4
  
  /* 
  	Populate #CustomPassCombo with SQL login names and their creation dates
  	cross applied with the values in the @CommonTerms table variable 
  */
  INSERT INTO #CustomPassCombo
  			(
  				[InstanceInfo], 
  				[CommonTerms], 
  				[LoginCreated]
  			)
  SELECT	
  	CAST([SL].[name] AS NVARCHAR(52))		AS [InstanceInfo], 
  	[CT].[CommTerms]						AS [CommonTerms], 
  	CONVERT(VARCHAR,[SL].[create_date],23)	AS [LoginCreated]
  FROM sys.sql_logins AS [SL]
  CROSS APPLY @CommonTerms AS [CT] 
  	WHERE [SL].[name] NOT LIKE N'##%##'
  	AND [SL].[is_disabled] IN (0, @IsDisabled)
  
  END
  IF (@UseInstanceInfo=1)
  BEGIN
  DECLARE @InstanceInfoList NVARCHAR(26);
  /* Set @InstanceInfoList */
  SELECT @InstanceInfoList = @InstanceName +'_'+ CONVERT(NVARCHAR,GETDATE(),23);
  /*
  	First pass - Insert only single terms w/ and w/o symbol wrapping into [dbo].[TempCustomPasswords]
  */
  
  	/* 
  		Database names wrapped between symbols
  	*/
  ;WITH [PDN] (PrefixDBNames) AS
  (
  	SELECT DISTINCT([SY].[Sym]+[InstanceInfo])
  	FROM #CustomPassCombo AS [DB]
  	CROSS APPLY @Symbols AS [SY]
  		WHERE [DB].[DatabaseCreated] IS NOT NULL
  )
  INSERT INTO [dbo].[TempCustomPasswords]
  			([Pass],[SourceList])
  	SELECT [PDN].[PrefixDBNames]+[SY].[Sym],@InstanceInfoList
  	FROM [PDN]
  	CROSS APPLY @Symbols AS [SY];
  
  	/* 
  		Add the SQL login names wrapped between symbols
  	*/
  ;WITH [PSL] (PrefixSQLLogins) AS
  (
  	SELECT DISTINCT([SY].[Sym]+[InstanceInfo])
  	FROM #CustomPassCombo AS [SL]
  	CROSS APPLY @Symbols AS [SY]
  		WHERE [SL].[LoginCreated] IS NOT NULL
  )
  INSERT INTO [dbo].[TempCustomPasswords]
  			([Pass], [SourceList])
  	SELECT [PSL].[PrefixSQLLogins]+[SY].[Sym],@InstanceInfoList
  	FROM [PSL]
  	CROSS APPLY @Symbols AS [SY];
  
  	/* 
  		Add the instance 
  	*/
  ;WITH [PI] (PrefixInstanceName) AS
  (
  	SELECT [SY].[Sym]+@InstanceName
  	FROM @Symbols AS [SY]
  )
  INSERT INTO [dbo].[TempCustomPasswords]
  			([Pass], [SourceList])
  	SELECT [PI].[PrefixInstanceName]+[SY].[Sym], @InstanceInfoList
  	FROM [PI]
  	CROSS APPLY @Symbols AS [SY];
  
  
  	/* 
  		Add instance creation year month and day 
  	*/
  		/* 
  			without '-' 
  		*/
  ;WITH [PICD] (PrefixInstCreateDate) AS
  (
  	SELECT [SY].[Sym]+REPLACE(@InstanceCreated,'-','')
  	FROM @Symbols AS [SY]
  )
  INSERT INTO [dbo].[TempCustomPasswords]
  			([Pass], [SourceList])
  	SELECT [PICD].[PrefixInstCreateDate]+[SY].[Sym], @InstanceInfoList
  	FROM [PICD]
  	CROSS APPLY @Symbols AS [SY];
  		/* 
  			with '-'
  		*/
  ;WITH [PICD] (PrefixInstCreateDate) AS
  	(
  		SELECT [SY].[Sym]+@InstanceCreated
  		FROM @Symbols AS [SY]
  	)
  INSERT INTO [dbo].[TempCustomPasswords]
  			([Pass], [SourceList])
  	SELECT [PICD].[PrefixInstCreateDate]+[SY].[Sym], @InstanceInfoList
  	FROM [PICD]
  	CROSS APPLY @Symbols AS [SY];
  
  
  /*	Second Pass
  	Concatenation between values stored in the #CustomPassCombo table 
  	(databse, common terms, login names database create date, login create date)
  	and other instance-related variables (Instance Create Date, Current Year, Instance Name)
  	wrapped between Symbols
  */
  ;WITH [CustomPassComboConcat] (PrefixCustomPassComboConcat) AS
  	(
  		/*
  		Database names (DatabaseCreated IS NOT NULL) concatenated with Common Terms
  		*/
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+[CommonTerms])
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database names concatenated with _ and Common Terms 
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+'_'+[CommonTerms])
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database names concatenated with lowercase Common Terms 
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+LOWER([CommonTerms]))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database names concatenated with _ and lowercase Common Terms 
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+'_'+LOWER([CommonTerms]))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database names concatenated with uppercase Common Terms 
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+UPPER([CommonTerms]))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database names concatenated with _ and uppercase Common Terms 
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+'_'+UPPER([CommonTerms]))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/* 
  			Uppercase Common Terms concatenated with @ and Database Names  
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+UPPER([CommonTerms])+'@'+[InstanceInfo] )
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/* 
  			Lowercase Common Terms concatenated with @ and Database Names  
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+LOWER([CommonTerms])+'@'+[InstanceInfo] )
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/* 
  			Common Terms concatenated with @ and Database Names  
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[CommonTerms]+'@'+[InstanceInfo] )
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/* 
  			Common Terms concatenated with Database Names  
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[CommonTerms]+[InstanceInfo] )
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/* 
  			Database Name concatenated with @CurrentYear
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CurrentYear)
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database name concatenated with database creation year
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+CAST([DatabaseCreated] AS VARCHAR(4)))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Login name concatenated with login creation year
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+CAST([LoginCreated] AS VARCHAR(4))) 
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [LoginCreated] IS NOT NULL
  		/*
  			@InstanceName concatenated with @InstanceCreated Year
  		*/
  		UNION 
  		SELECT [SY].[Sym]+@InstanceName+CAST(@InstanceCreated AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			@InstanceName concatenated with @InstanceCreated w/o -
  		*/
  		UNION 
  		SELECT [SY].[Sym]+@InstanceName+REPLACE(@InstanceCreated,'-','')
  		FROM @Symbols AS [SY]
  		/*
  			Instance name concatenated with common terms
  		*/
  		UNION 
  		SELECT DISTINCT([SY].[Sym]+@InstanceName+[CommonTerms])
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  	)
  	/*
  		Add symbols to the end of the combinations 
  		from [CustomPassComboConcat] and insert them into the table
  	*/
  	INSERT INTO [dbo].[TempCustomPasswords]
  				([Pass], [SourceList])
  	SELECT [CPCC].[PrefixCustomPassComboConcat]+[SY].[Sym], @InstanceInfoList
  	FROM [CustomPassComboConcat] AS [CPCC]
  	CROSS APPLY @Symbols AS [SY];
  
  END
  
  	/*	Combinations with @CustomTerm if provided */
  
  IF (@CustomTerm <> N'')
  BEGIN
  /* Declare CustomTermList */
  DECLARE @CustomTermList NVARCHAR(42);
  /* Set @CustomTermList */
  SET @CustomTermList = @CustomTerm+'_'+ CONVERT(NVARCHAR,GETDATE(),23);
  ;WITH [CustomTermCombos]  ([PrefixCustTermCombos]) AS
  (		/*
  			CustomTerm only
  		*/
  		SELECT [SY].[Sym]+@CustomTerm
  		FROM @Symbols AS [SY]
  		/*
  			@CustomTerm concatenated with @CurrentYear 
  		*/
  		UNION
  		SELECT	[SY].[Sym]+@CustomTerm+@CurrentYear
  		FROM @Symbols AS [SY]
  		/*
  			@CustomTerm concatenated with @CurrentYear-1
  		*/
  		UNION
  		SELECT	[SY].[Sym]+@CustomTerm+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-1,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			@CustomTerm concatenated with @CurrentYear-2
  		*/
  		UNION
  		SELECT	[SY].[Sym]+@CustomTerm+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-2,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			@CustomTerm concatenated with @CurrentYear-3
  		*/
  		UNION
  		SELECT	[SY].[Sym]+@CustomTerm+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-3,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  
  		/*
  			Lazy l33t on @CustomTerm - the most common l33t-style replacements 
  			concatenated with @CurrentYear 
  		*/
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','@'),'o','0'),'i','1'),'E','3'),'A','4'),'O','0'),'I','1')+@CurrentYear
  		FROM @Symbols AS [SY]
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','4'),'o','0'),'i','1'),'E','3'),'A','4'),'O','0'),'I','1')+@CurrentYear
  		FROM @Symbols AS [SY]
  		/*
  			Lazy l33t on @CustomTerm - the most common l33t-style replacements 
  			concatenated with @CurrentYear-1 
  		*/
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','@'),'o','0'),'i','1'),'E','3'),'A','@'),'O','0'),'I','1')+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-1,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','4'),'o','0'),'i','1'),'E','3'),'A','4'),'O','0'),'I','1')+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-1,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			Lazy l33t on @CustomTerm - the most common l33t-style replacements 
  			concatenated with @CurrentYear-2 
  		*/
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','@'),'o','0'),'i','1'),'E','3'),'A','@'),'O','0'),'I','1')+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-2,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','4'),'o','0'),'i','1'),'E','3'),'A','4'),'O','0'),'I','1')+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-2,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			Lazy l33t on @CustomTerm - the most common l33t-style replacements 
  			concatenated with @CurrentYear-3 
  		*/
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','@'),'o','0'),'i','1'),'E','3'),'A','@'),'O','0'),'I','1')+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-3,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		UNION
  		SELECT	[SY].[Sym]+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CustomTerm,'e','3'),'a','4'),'o','0'),'i','1'),'E','3'),'A','4'),'O','0'),'I','1')+CAST(CONVERT(VARCHAR,DATEADD(YEAR,-3,GETDATE()),23) AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			Database Name concatenated with @CustomTerm 
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CustomTerm)
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database Name concatenated with @CustomTerm and Current Year
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CustomTerm+@CurrentYear)
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Database Name concatenated with @CustomTerm and Database Creation Year
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CustomTerm+CAST([DatabaseCreated] AS VARCHAR(4)))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [DatabaseCreated] IS NOT NULL
  		/*
  			Login Name concatenated with @CustomTerm
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CustomTerm)
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [LoginCreated] IS NOT NULL
  		/*
  			Login Name concatenated with @CustomTerm and Current Year
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CustomTerm+@CurrentYear)
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [LoginCreated] IS NOT NULL
  		/*
  			Login Name concatenated with @CustomTerm and Login Creation Year
  		*/
  		UNION
  		SELECT	DISTINCT([SY].[Sym]+[InstanceInfo]+@CustomTerm+CAST([LoginCreated] AS VARCHAR(4)))
  		FROM #CustomPassCombo
  		CROSS APPLY @Symbols AS [SY]
  			WHERE [LoginCreated] IS NOT NULL
  		/*
  			@InstanceName concatenated with @CustomTerm
  		*/
  		UNION 
  		SELECT [SY].[Sym]+@InstanceName+@CustomTerm
  		FROM @Symbols AS [SY]
  		/*
  			@InstanceName concatenated with @CustomTerm and @InstanceCreated Year
  		*/
  		UNION 
  		SELECT [SY].[Sym]+@InstanceName+@CustomTerm+CAST(@InstanceCreated AS VARCHAR(4))
  		FROM @Symbols AS [SY]
  		/*
  			@InstanceName concatenated with @CustomTerm and @InstanceCreated Year Month without '-'
  		*/
  		UNION
  		SELECT [SY].[Sym]+@InstanceName+@CustomTerm+REPLACE(CAST(@InstanceCreated AS VARCHAR(7)),'-','')
  		FROM @Symbols AS [SY]
  		/*
  			@InstanceName concatenated with @CustomTerm and @InstanceCreated Year Month Day without '-'
  		*/
  		UNION
  		SELECT [SY].[Sym]+@InstanceName+@CustomTerm+REPLACE(CAST(@InstanceCreated AS VARCHAR(10)),'-','')
  		FROM @Symbols AS [SY]
  )
  	INSERT INTO [dbo].[TempCustomPasswords]
  				([Pass],[SourceList])
  	SELECT [CTC].[PrefixCustTermCombos]+[SY].[Sym], @CustomTermList
  	FROM [CustomTermCombos] AS [CTC]
  	CROSS APPLY @Symbols AS [SY];
  END;
  
  
  
  /* Handle Source Lists */
    IF (@SourceLists = N'ALL')
    BEGIN
   INSERT INTO #SelectedLists ([ListName],[Selected])
   SELECT DISTINCT([SourceList]), 1 FROM [dbo].[Passwords];
    END;
    ELSE IF (@SourceLists <> N'ALL')
    BEGIN
    SET @SourceLists = REPLACE(@SourceLists, CHAR(10), N'');
    SET @SourceLists = REPLACE(@SourceLists, CHAR(13), N'');
    WHILE CHARINDEX(@Delim + ' ', @SourceLists) > 0 
  	SET @SourceLists = REPLACE(@SourceLists, @Delim + N' ', @Delim);
    WHILE CHARINDEX(N' ' + @Delim, @SourceLists) > 0 
  	SET @SourceLists = REPLACE(@SourceLists, N' ' + @Delim, @Delim);
  
    SET @SourceLists = LTRIM(RTRIM(@SourceLists));
  
    WITH Lists1 (StartPosition, EndPosition, ListItem) AS
    (
    SELECT 1 AS StartPosition,
           ISNULL(NULLIF(CHARINDEX(@Delim, @SourceLists, 1), 0), LEN(@SourceLists) + 1) AS EndPosition,
           SUBSTRING(@SourceLists, 1, ISNULL(NULLIF(CHARINDEX(@Delim, @SourceLists, 1), 0), LEN(@SourceLists) + 1) - 1) AS ListItem
    WHERE @SourceLists IS NOT NULL
    UNION ALL
    SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
           ISNULL(NULLIF(CHARINDEX(@Delim, @SourceLists, EndPosition + 1), 0), LEN(@SourceLists) + 1) AS EndPosition,
           SUBSTRING(@SourceLists, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@Delim, @SourceLists, EndPosition + 1), 0), LEN(@SourceLists) + 1) - EndPosition - 1) AS ListItem
    FROM Lists1
    WHERE EndPosition < LEN(@SourceLists) + 1
    ),
    Lists2 (ListName, StartPosition, Selected) AS
    (
    SELECT CASE WHEN ListItem LIKE N'-%' THEN RIGHT(ListItem,LEN(ListItem) - 1) ELSE ListItem END AS ListItem,
           StartPosition,
           CASE WHEN ListItem LIKE N'-%' THEN 0 ELSE 1 END AS Selected
    FROM Lists1
    )
    INSERT INTO #SelectedLists (ListName,StartPosition, Selected)
    SELECT ListName,
           StartPosition,
           Selected
    FROM Lists2
    OPTION (MAXRECURSION 0)
    END
  
  
  IF (@ResultsToTable = 1)
  BEGIN 
   IF NOT EXISTS  (SELECT * FROM sys.objects 
  	WHERE [object_id] = OBJECT_ID(N'[dbo].[SQLAuditResults]') AND [type] in (N'U'))
  	BEGIN
  		CREATE TABLE [dbo].[SQLAuditResults]
  		(
  			[ID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
  			[LoginName] SYSNAME,
  			[Password] NVARCHAR(128),
  			[FromSourceList] NVARCHAR(128)
  		);
  	END
  INSERT INTO [dbo].[SQLAuditResults] 
  			(
  				[LoginName],
  				[Password],
  				[FromSourceList]			
  			)
  SELECT	[SL].[name]			AS [LoginName],
  		[P].[Pass]			AS [Password],
  		[P].[SourceList]	AS [FromSourceList]
  FROM sys.sql_logins AS [SL]
  INNER JOIN [dbo].[Passwords] AS [P]
  	ON PWDCOMPARE([P].[Pass],[SL].[password_hash]) = 1
  WHERE
    /* Filtering based on @IgnorePolicy */
  	(
  		(
  			[SL].[is_policy_checked] = 1 
  		AND LEN([P].[Pass])>=8
  		)
  	OR 
  		(
  			[SL].[is_policy_checked] IN (0, @IgnorePolicy) 
  		AND LEN([P].[Pass])>=0
  		)
  	)
  	/* Filtering based on @IsDisabled (@ExcludeDisabled) */
  	AND	[SL].[is_disabled] IN (0, @IsDisabled)
  	/* Filtering source lists*/
  	AND [P].[SourceList] IN (SELECT [ListName] 
  							FROM #SelectedLists)
  	/* Filter out certificate-based accounts */
  	AND [SL].[name] NOT LIKE N'##%##'
  UNION 
  SELECT	[SL].[name]			AS [LoginName],
  		[TP].[Pass]			AS [Password],
  		[TP].[SourceList]	AS [FromSourceList]
  FROM sys.sql_logins AS [SL]
  INNER JOIN [dbo].[TempCustomPasswords] AS [TP]
  ON PWDCOMPARE([TP].[Pass],[SL].[password_hash]) = 1
  WHERE
    /* Filtering based on @IgnorePolicy */
  	(
  		(
  			[SL].[is_policy_checked] = 1 
  		AND LEN([TP].[Pass])>=8
  		)
  	OR 
  		(
  			[SL].[is_policy_checked] IN (0, @IgnorePolicy) 
  		AND LEN([TP].[Pass])>=0
  		)
  	)
  	/* Filtering based on @IsDisabled (@ExcludeDisabled) */
  	AND	[SL].[is_disabled] IN (0, @IsDisabled)
  	/* Filter out certificate-based accounts */
  	AND [SL].[name] NOT LIKE N'##%##';
  END;
  ELSE IF (@ResultsToTable = 0)
  BEGIN
  SELECT	[SL].[name]			AS [LoginName],
  		[P].[Pass]			AS [Password],
  		[P].[SourceList]	AS [FromSourceList]
  FROM sys.sql_logins AS [SL]
  INNER JOIN [dbo].[Passwords] AS [P]
  	ON PWDCOMPARE([P].[Pass],[SL].[password_hash]) = 1
  WHERE
    /* Filtering based on @IgnorePolicy */
  	(
  		(
  			[SL].[is_policy_checked] = 1 
  		AND LEN([P].[Pass])>=8
  		)
  	OR 
  		(
  			[SL].[is_policy_checked] IN (0, @IgnorePolicy) 
  		AND LEN([P].[Pass])>=0
  		)
  	)
  	/* Filtering based on @IsDisabled (@ExcludeDisabled) */
  	AND	[SL].[is_disabled] IN (0, @IsDisabled)
  	/* Filtering source lists */
  	AND [P].[SourceList] IN (SELECT [ListName] 
  							FROM #SelectedLists)
  	/* Filter out certificate-based accounts */
  	AND [SL].[name] NOT LIKE N'##%##'
  	/* Union with instance-specific and/or custom term based check */
  UNION 
  SELECT	[SL].[name]			AS [LoginName],
  		[TP].[Pass]			AS [Password],
  		[TP].[SourceList]	AS [FromSourceList]
  FROM sys.sql_logins AS [SL]
  INNER JOIN [dbo].[TempCustomPasswords] AS [TP]
  ON PWDCOMPARE([TP].[Pass],[SL].[password_hash]) = 1
  WHERE
    /*Filtering based on @IgnorePolicy */
  	(
  		(
  			[SL].[is_policy_checked] = 1 
  		AND LEN([TP].[Pass])>=8
  		)
  	OR 
  		(
  			[SL].[is_policy_checked] IN (0, @IgnorePolicy) 
  		AND LEN([TP].[Pass])>=0
  		)
  	)
  	/*Filtering based on @IsDisabled (@ExcludeDisabled)*/
  	AND	[SL].[is_disabled] IN (0, @IsDisabled)
  	/*Filter out certificate-based accounts*/
  	AND [SL].[name] NOT LIKE N'##%##';
  END;
  
  /* Clean up */
  
    IF OBJECT_ID(N'tempdb..#CustomPassCombo') IS NOT NULL
  	BEGIN
  			DROP TABLE #CustomPassCombo;
  	END;
    IF OBJECT_ID(N'tempdb..#SelectedLists') IS NOT NULL
  	BEGIN
  			DROP TABLE #SelectedLists;
  	END;
  IF EXISTS(SELECT * FROM sys.objects 
  		WHERE [object_id] = OBJECT_ID(N'[dbo].[TempCustomPasswords]') AND [type] in (N'U'))
  	BEGIN
  		DROP TABLE [dbo].[TempCustomPasswords];
  	END;
  IF (@UseInstanceInfo=1 OR @CustomTerm <> N'')
  BEGIN
  	IF EXISTS(SELECT [OB].* 
  				FROM sys.objects AS [OB]
  				INNER JOIN sys.partitions [PT]
  				ON [OB].[object_id] = [PT].[object_id]
  					WHERE [OB].[object_id] = OBJECT_ID(N'[dbo].[Passwords]') AND [type] in (N'U')
  						AND [PT].[rows] = 0)
  	BEGIN
  	DROP TABLE [dbo].[Passwords];
  	END;
  END; 
  