As DBAs, we often wear many hats. Sometimes we focus on administration, sometimes architecture, and sometimes development.

Recently I've found myself answering questions for Sarbanes-Oxley (SOX) auditors. These questions typically start with an email from an auditor asking for information about the production SQL Server environment. Some of the questions I've received include:

• Can you provide a list of all production logins including databases they have access to?
• Do you have log backups for all production databases?
• Which logins have create or alter object privileges?

Answering any of these questions is easier when you have just one server, but when you have a couple dozen servers with a few hundred databases, it's a little more challenging. So the first thing I thought about was how can I automate these tasks and iterate through multiple servers. Although I kicked around the idea of rewriting the utility I created a few years ago, SQLDumpSec, ultimately I ended up taking a simpler approach using Excel and SQL-DMO VBA macros to pull security and configuration data from multiple SQL Servers. The SQLDMO_Macros spreadsheet contains five macros which have been tested against SQL 2000 and strictly use Excel VBA and SQL-DMO to get security/configuration information.

Let's look at one of the SQL-DMO macros, getLinkSrvLogins as an

example.

Option Explicit
Option Compare Text
Public intCount As Integer
Public Sub getLinkedSrvLogins()
Dim ws As Worksheet
Dim cn As Range
Dim strServer As String
Dim c1 As Range

Call Utility.AddSheetIfNotExists("LinkedServerLogins")
Set ws = Worksheets("LinkedServerLogins")
ws.Cells.Clear

 With Worksheets("LinkedServerLogins").Rows(1)
 .Font.Bold = True
 .Cells(1, 1).Value = "Server"
 .Cells(1, 1).Font.Bold = True
 .Cells(1, 1).Interior.ColorIndex = 6
 .Cells(1, 2).Value = "LinkedServer"
 .Cells(1, 2).Font.Bold = True
 .Cells(1, 2).Interior.ColorIndex = 6
 .Cells(1, 3).Value = "DataSource"
 .Cells(1, 3).Font.Bold = True
 .Cells(1, 3).Interior.ColorIndex = 6
 .Cells(1, 4).Value = "LocalLogin"
 .Cells(1, 4).Font.Bold = True
 .Cells(1, 4).Interior.ColorIndex = 6
 .Cells(1, 5).Value = "RemoteUser"
 .Cells(1, 5).Font.Bold = True
 .Cells(1, 5).Interior.ColorIndex = 6
 .Cells(1, 6).Value = "Impersonate"
 .Cells(1, 6).Font.Bold = True
 .Cells(1, 6).Interior.ColorIndex = 6

 End With
 ' Initialize pub counter
 intCount = 1
 
 Set ws = Worksheets("Config")
 For Each c1 In ws.Range("Server")
 Set cn = c1.Offset(0, 1)
 If c1 = "" Then
 Exit For
 End If
 strServer = c1
 strServer = Trim(strServer)
 Call ListLinkSrvLogins(strServer)
 Next
 With Worksheets("LinkedServerLogins").Columns("A:F")
 .AutoFit
 End With

End Sub

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
 
 'strServer = dmoServer.Name
 strLinkSrv = dmoLinkSrv.Name
 strSrc = dmoLinkSrv.DataSource
 For Each dmoLinkSrvLogin In dmoLinkSrv.LinkedServerLogins
 'For intLogin = 1 To dmoLinkSrv.LinkedServerLogins.Count
 strLocalLogin = dmoLinkSrvLogin.LocalLogin
 strRemoteUser = dmoLinkSrvLogin.RemoteUser
 bnImpersonate = dmoLinkSrvLogin.Impersonate
 
 ' Increment line number
 intCount = intCount + 1
 Call AddToSheet(intCount, strServer, strLinkSrv, strSrc, strLocalLogin, strRemoteUser, bnImpersonate)

 'Next
 Next
 Next
 
 
 ' Cleanup objects
 Set dmoLinkSrvLogin = Nothing
 Set dmoLinkSrv = Nothing
 dmoServer.Close
 Set dmoServer = Nothing
 
End Function

Public Function AddToSheet(intCount As Integer, strServer As String, strLinkSrv As String, _
 strSrc As String, strLocalLogin As String, strRemoteUser As String, bnImpersonate As Boolean)
 With Worksheets("LinkedServerLogins").Rows(intCount)
 .Cells(1, 1).Value = strServer
 .Cells(1, 2).Value = strLinkSrv
 .Cells(1, 3).Value = strSrc
 .Cells(1, 4).Value = strLocalLogin
 .Cells(1, 5).Value = strRemoteUser
 .Cells(1, 6).Value = bnImpersonate
 End With

End Function

Looking at the code, first a worksheet called "LinkedServerLogins" is added if it doesn't already exits. Next the header row is created. The code then iterates through the list of servers specified in Config tab, Servers range calling the SQL-DMO function "ListLinkSrvLogins." And here's why I love SQL-DMO only 11 lines of simple code in the "ListLinkSrvLogins" function gives me all the linked server login mappings. Finally the last function "AddToSheet" outputs the line to Excel. The other SQL-DMO macros follow the same pattern, iterate through the list of SQL Servers, call the SQL-DMO function, and write the output to Excel.

The VBA code requires SQL Server client tools and Microsoft Excel to be installed on the machine you are using. To use the macros, setup your server names in the Config tab and follow the instructions in the Help tab of the Excel spreadsheet. By using a little SQL-DMO and Excel VBA code you can quickly create SOX reports, eliminating the need for writing a bunch of SQL scripts and manually piecing together the output.