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:
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 🙂