< Back
Invoke-DbaDbPiiScan
Post
NAME Invoke-DbaDbPiiScan
SYNOPSIS
Command to return any columns that could potentially contain PII (Personal Identifiable Information)
SYNTAX
Invoke-DbaDbPiiScan [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-Database] <System.String[]>] [[-Table] <System.String[]>] [[-Column]
<System.String[]>] [[-Country] <System.String[]>] [[-CountryCode] <System.String[]>] [[-ExcludeTable]
<System.String[]>] [[-ExcludeColumn] <System.String[]>] [[-SampleCount] <Int>] [[-KnownNameFilePath] <String>]
[[-PatternFilePath] <String>] [-ExcludeDefaultKnownName <Switch>] [-ExcludeDefaultPattern <Switch>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
This command will go through the tables in your database and assess each column.
It will first check the columns names if it was named in such a way that it would indicate PII.
The next thing that it will do is pattern recognition by looking into the data from the table.
PARAMETERS
-Column [<System.String[]>]
Column(s) to process. By default all the columns will be processed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Country [<System.String[]>]
Filter out the patterns and known types for one or more countries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-CountryCode [<System.String[]>]
Filter out the patterns and known types for one or more country code
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.String[]>]
Databases to process through
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeColumn [<System.String[]>]
Exclude certain columns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDefaultKnownName [<Switch>]
Excludes the default known names
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDefaultPattern [<Switch>]
Excludes the default patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeTable [<System.String[]>]
Exclude certain tables
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KnownNameFilePath [<String>]
Points to a file containing the custom known names
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PatternFilePath [<String>]
Points to a file containing the custom patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SampleCount [<Int>]
Amount of rows to sample to make an assessment. The default is 100
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Table [<System.String[]>]
Table(s) to process. By default all the tables will be processed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Data Masking, Database, Personal Information, GDPR
Author: Sander Stad (@sqlstad, sqlstad.nl)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db1
Scan the database db1 on instance sql1
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1, sql2 -Database db1, db2
Scan multiple databases on multiple instances
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db2 -ExcludeColumn firstname
Scan database db2 but exclude the column firstname
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db2 -CountryCode US
Scan database db2 but only apply data patterns used for the United States
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db1 -PatternFilePath c:\\pii\\patterns.json
Scans db1 on instance sql1 with additional custom patterns
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db1 -PatternFilePath c:\\pii\\patterns.json
-ExcludeDefaultPattern
Scans db1 on instance sql1 with additional custom patterns, excluding the default patterns
RELATED LINKS
https://dbatools.io/Invoke-DbaDbPiiScan
SYNOPSIS
Command to return any columns that could potentially contain PII (Personal Identifiable Information)
SYNTAX
Invoke-DbaDbPiiScan [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-Database] <System.String[]>] [[-Table] <System.String[]>] [[-Column]
<System.String[]>] [[-Country] <System.String[]>] [[-CountryCode] <System.String[]>] [[-ExcludeTable]
<System.String[]>] [[-ExcludeColumn] <System.String[]>] [[-SampleCount] <Int>] [[-KnownNameFilePath] <String>]
[[-PatternFilePath] <String>] [-ExcludeDefaultKnownName <Switch>] [-ExcludeDefaultPattern <Switch>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
This command will go through the tables in your database and assess each column.
It will first check the columns names if it was named in such a way that it would indicate PII.
The next thing that it will do is pattern recognition by looking into the data from the table.
PARAMETERS
-Column [<System.String[]>]
Column(s) to process. By default all the columns will be processed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Country [<System.String[]>]
Filter out the patterns and known types for one or more countries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-CountryCode [<System.String[]>]
Filter out the patterns and known types for one or more country code
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.String[]>]
Databases to process through
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeColumn [<System.String[]>]
Exclude certain columns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDefaultKnownName [<Switch>]
Excludes the default known names
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDefaultPattern [<Switch>]
Excludes the default patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeTable [<System.String[]>]
Exclude certain tables
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KnownNameFilePath [<String>]
Points to a file containing the custom known names
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PatternFilePath [<String>]
Points to a file containing the custom patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SampleCount [<Int>]
Amount of rows to sample to make an assessment. The default is 100
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Table [<System.String[]>]
Table(s) to process. By default all the tables will be processed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Data Masking, Database, Personal Information, GDPR
Author: Sander Stad (@sqlstad, sqlstad.nl)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db1
Scan the database db1 on instance sql1
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1, sql2 -Database db1, db2
Scan multiple databases on multiple instances
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db2 -ExcludeColumn firstname
Scan database db2 but exclude the column firstname
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db2 -CountryCode US
Scan database db2 but only apply data patterns used for the United States
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db1 -PatternFilePath c:\\pii\\patterns.json
Scans db1 on instance sql1 with additional custom patterns
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Invoke-DbaDbPiiScan -SqlInstance sql1 -Database db1 -PatternFilePath c:\\pii\\patterns.json
-ExcludeDefaultPattern
Scans db1 on instance sql1 with additional custom patterns, excluding the default patterns
RELATED LINKS
https://dbatools.io/Invoke-DbaDbPiiScan