T-SQL Tuesday #130 – Automate Permissions Audits2 min read

This month’s T-SQL Tuesday is being hosted by Elizabeth Noble. The subject chosen “what have you automated to make your life easier?” For me the topic is near and dear to my heart. I was honored to host T-SQL Tuesday #110 and had a similar topic. It was awesome to see peoples automation stories. That was about 18 months ago, I am excited to see the responses that come in 2020.

My post for T-SQL Tuesday #130 is going to be how I automated my companies SQL Server Permissions Audit process. Even though this only occurs every quarter it is something that I as the DBA never look forward to and the business partners never really enjoy. In an effort to streamline the process from start to finish I built automation using PowerShell, dbatools and ImportExcel. The process now builds the permissions audit file, extracts individual users from Active Directory and e-mails the information to the appropriate team(s) for review.

Extracting Permissions from SQL

Firstly, I am using the Get-DbaUserPermission dbatools function to gather account permissions from the SQL Server.

Inserting Information into Excel

Next, after gathering the permissions the results are passed to the Export-Excel ImportExcel function to build the Excel file. Then a few Add-ConditionalFormatting calls, this allows the changing of cell colors based on specific text values.

Connecting to Active Directory for User Information

The Get-AdGroupMember Active Directory function allows you to pass in an Active Directory Group Name and return all the Active Directory User Name or Service Accounts. This is very helpful for people performing the permissions audit to allow them to gain insight in to “who” in a Group has access to the data.

Attaching final output and Sending E-mail

Finally, using the Send-MailMessage function allows e-mail to be sent with the Excel file created as an attachment to a Distribution List.

Conclusion

See an example of our output from the automation created.

Creating automation is always fun, especially when you have a defined process that you want to replicate.