Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
examples of using dbaSecurityScan to bring SQL Server database security back inline with a baseline config
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment