examples of using dbaSecurityScan to bring SQL Server database security back inline with a baseline config

dbaSecurityScanFixExamples.ps1

# This is based on the module state on 18th April 2020, it will have moved on since then!

Import-module dbaSecurityScan

# Create config and test against a new database
$config = New-DssConfig -SqlInstance server1\sql2017 -Database db1
$config | ConvertTo-Json -Depth 5 | Out-File \\file\store\db1-SecurityConfig.json

# time passes and we want to check for security drift
# hydrate the config back into an object

$config = ConvertFrom-Json -InputObject (Get-Content \\file\store\db1-SecurityConfig.json -raw)

# Test the database against the config and vice versa
# This will run all the tests and store the results of pass/fail in $testresult.

$testResults = Invoke-DssTest -SqlInstance server1\sql2017 -Database db1 -Config $config 

# If you don't want to see all of the Pester output as well, then you can

$testResults = Invoke-DssTest -SqlInstance server1\sql2017 -Database db1 -Config $config -Quiet

# Now we can use the results to bring the database back to security baseline
# Let's first sanity check any fixes, -OutputOnly will just output the intended actions and not apply any.

$fixResults = Reset-DssSecurity -SqlInstance server1\sql2017 -Database db1 -TestResult $testResults -OutputOnly

# Once you've approved the changes, then can be applied like so:
$fixResults = Reset-DssSecurity -SqlInstance server1\sql2017 -Database db1 -TestResult $testResults