As a database administrator (DBA), we often find ourselves answering questions from auditors regarding the security and configuration of our SQL Server environments. These questions can be time-consuming to answer, especially when dealing with multiple servers and databases. In this article, we will explore how to automate these tasks using SQL-DMO and Excel VBA macros.

One common request from auditors is to provide a list of all production logins and the databases they have access to. This information can be easily obtained using SQL-DMO, a set of COM objects provided by Microsoft for managing SQL Server. By leveraging the power of SQL-DMO and Excel VBA macros, we can quickly generate reports that satisfy auditor requirements.

Let’s take a look at an example macro called “getLinkSrvLogins” that retrieves linked server login mappings:

Public Function ListLinkSrvLogins(strServer As String)
    ' SQLDMO variables
    Dim dmoServer As SQLServer2
    Dim dmoLinkSrv As SQLDMO.LinkedServer2
    Dim dmoLinkSrvLogin As SQLDMO.LinkedServerLogin
    
    ' Counter variables
    Dim intLogin As Integer
    
    ' String variables
    Dim strLinkSrv As String
    Dim strSrc As String
    Dim strLocalLogin As String
    Dim strRemoteUser As String
    Dim bnImpersonate As Boolean
    
    Set dmoServer = New SQLDMO.SQLServer2
    dmoServer.LoginSecure = True
    dmoServer.Connect strServer
    
    For Each dmoLinkSrv In dmoServer.LinkedServers
        strLinkSrv = dmoLinkSrv.Name
        strSrc = dmoLinkSrv.DataSource
        
        For Each dmoLinkSrvLogin In dmoLinkSrv.LinkedServerLogins
            strLocalLogin = dmoLinkSrvLogin.LocalLogin
            strRemoteUser = dmoLinkSrvLogin.RemoteUser
            bnImpersonate = dmoLinkSrvLogin.Impersonate
            
            ' Output to Excel
            Call AddToSheet(intCount, strServer, strLinkSrv, strSrc, strLocalLogin, strRemoteUser, bnImpersonate)
        Next
    Next
    
    ' Cleanup objects
    Set dmoLinkSrvLogin = Nothing
    Set dmoLinkSrv = Nothing
    dmoServer.Close
    Set dmoServer = Nothing
End Function

In this macro, we first connect to the SQL Server using SQLDMO.SQLServer2 object and set the LoginSecure property to true to use Windows authentication. We then iterate through each linked server and retrieve the linked server login mappings using the LinkedServerLogins property. Finally, we output the results to an Excel spreadsheet using the AddToSheet function.

By following a similar pattern, we can create macros to generate reports for other auditor queries, such as log backups for all production databases or logins with create or alter object privileges. The possibilities are endless.

To use these macros, you will need to have SQL Server client tools and Microsoft Excel installed on your machine. Simply set up your server names in the Config tab of the Excel spreadsheet and follow the instructions provided in the Help tab.

By leveraging the power of SQL-DMO and Excel VBA, you can automate the process of generating SOX reports, eliminating the need for writing complex SQL scripts and manually piecing together the output. This not only saves time but also ensures accuracy and consistency in your auditing process.

So the next time you find yourself answering questions from auditors, consider using SQL-DMO and Excel VBA to automate the process and make your life as a DBA a little bit easier.