SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

By Chad Miller,

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.

GenDropLoginScript Generates a drop login script with user database drops
getDBRecovery Reports Database recovery model settings
getLinkSrvLogins Reports linked server login mappings
getLogins Reports logins and database DB access of logins
getPriv Reports 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")

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)
With Worksheets("LinkedServerLogins").Columns("A:F")
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)


' Cleanup objects
Set dmoLinkSrvLogin = Nothing
Set dmoLinkSrv = Nothing
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.



Total article views: 11172 | Views in the last 30 days: 2
Related Articles

SQL Server – String_Escape function in SQL Server 2016

Hi friends, in SQL server 2016 another string function that introduced is String_Escape. This functi...


New Built-in Function STRING_AGG() – SQL Server 2017

SQL Server 2017 introduces a set of useful functions like STRING_AGG(), STRING_SPLIT(), TRIM()… and ...


SQL Server – STRING_SPLIT function in SQL Server 2016

Hi friends, in SQL server 2016 another new function STRING_SPLIT gets introduced which helps in spli...


CONCAT() string function–SQL Server 2012

As the name indicates, CONCAT() will concatenate two or more strings. This is one of the two string ...


Split String Function

This In-line Table Valued Function returns a table with individual records parsed from a string.

sql server 7