SQLServerCentral Article

Use SQL-DMO and Excel to Quickly Create Reports for Auditors

,

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.

GenDropLoginScriptGenerates a drop login script with user database drops
getDBRecoveryReports Database recovery model settings
getLinkSrvLoginsReports linked server login mappings
getLoginsReports logins and database DB access of logins
getPrivReports logins which have create object privileges

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.

Resources

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating