While doing some work on my materials for SqlBits training day I started thinking about a few of the problems with managing SQL Server permissions.

How easy it to audit them? If someone asks you the DBA exactly who has access to object A, can you tell them? How do people get access to that object, is it via a role, a schema or an explicit permission?

Is that information in an easy to read or manipulate manner?

How do you ensure that permissions persist between upgrades? I’ve certainly seen 3rd party upgrades that have reset database level permissions. Do you have a mechanism to check every permission and put them back as they were?

We’re all doing the devops these days. Our database schema is source controlled, and we’re deploying it incrementally in pipelines and testing it. But are we doing that with our database security?

So in the classic open source way, I decided to scratch my own itch by writing something. That something is dbaSecurityScan, a PowerShell module that aims to offer a solution for all of the above.

The core functionality atm allows you to export the current security state of your database, based on these 4 models:

  • Role based
  • User based
  • Schema based
  • Object based

You can also return all of them, or a combination of whatever you need.

At the time of writing, getting the security information and testing it is implemented, and you can try it out like this:

Import-module DssSecurityScan
Create config and test against a new database
$config = New-DssConfig SqlInstance server1\sql2017 Database oldProd
Invoke-DssTest SqlInstance server2\sql2019 Database newProd Config $config
Save a config, and then use it audit after a change
New-DssConfig SqlInstance server1\sql2017 Database oldProd ConfigPath ~/git/dbSourceControl/permissions.json
Start-Sleep Seconds 10080
$config = ConvertFrom-Json InputObject (Get-Content ~/git/dbSourceControl/permissions.json raw)
Invoke-DssTest SqlInstance server2\sql2019 Database newProd Config $config

If you’ve got any suggestions for features, or want to lend a hand then please head over to dbaSecurityScan and raise an issue or pull request respectively 🙂