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.
The post T-SQL Tuesday #130 – Automate Permissions Audits appeared first on GarryBargsley.com.