Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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")
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:

SQLDMO_Macros.xls
Total article views: 11143 | Views in the last 30 days: 6
 
Related Articles
BLOG

CONCAT() string function–SQL Server 2012

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

ARTICLE

Excel Function Returns Cell Address

This article details an Excel 2010 function to return the cell address of min and max functions.

FORUM

SQL Functions in Excel?

Can a scalar SQL function be used instead of Excel formulas?

FORUM

Reverse string without built in functions

reverse string

FORUM

Excel Functions in SQL server2005

How to implement LINEST EXCEL Unction in SQL server 2005

Tags
miscellaneous    
programming    
sql server 7    
sql-dmo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones