SQL Server Audit Script - Part 2

In the first part of this article I mentioned that a SQL Server Audit is often a time consuming manual process and provided screenshots from a script that makes the job much easier. In the second part I give you the code and explain how to set it up.

First, the code. Create a file called SQL Audit.hta, open it with notepad and paste the following into it:

<html>
<head>
<title>SQL Audit</title>
<HTA:APPLICATION ID="SQLAudit" 
    APPLICATIONNAME="SQLAudit" 
    CAPTION="yes"
    SHOWINTASKBAR="yes"
    SINGLEINSTANCE="yes"
    SYSMENU="yes"
    WINDOWSTATE="maximize"
    SCROLL="yes" />
<style></style>
</head>
<script language="vbscript">
Const NONE = 0
Const SERVER_CONFIG = 1
Const INSTANCE_SUMMARY = 2

Dim m_state
Dim m_index
Dim m_timerId
Dim m_filterGroup

Dim m_server_name()
Dim m_server_desc()
Dim m_server_domain()
Dim m_instance_name()
Dim m_instance_desc()
Dim m_instance_auth()
Dim m_instance_uid()
Dim m_instance_pwd()

Sub window_OnLoad()
	Set xmlDoc = CreateObject("MSXML2.DOMDocument")
	xmlDoc.load ("SQL Servers.xml")
  
	Set objOption = document.createElement("OPTION")
	cboGroup.options.add objOption
	objOption.innerText = "All"
	objOption.value = "All"

Set nodeList = xmlDoc.documentElement.selectNodes("group") 
	For Each node In nodeList
		Set objOption = document.createElement("OPTION")
		cboGroup.options.add objOption
		objOption.innerText = node.attributes.GetNamedItem("name").NodeValue
		objOption.value = node.attributes.GetNamedItem("name").NodeValue
	Next
	cboGroup_onchange
End Sub

Sub cboGroup_onchange()
	Set xmlDoc = CreateObject("MSXML2.DOMDocument")
	xmlDoc.load ("SQL Servers.xml")
  
	groupToFilter = cboGroup.value

If groupToFilter = m_filterGroup Then
		Exit Sub
	End If

For i = tblServerSettings.rows.length - 1 To 1 Step -1
		tblServerSettings.deleteRow(i)
	Next

For i = tblInstanceSettings.rows.length - 1 To 1 Step -1
		tblInstanceSettings.deleteRow(i)
	Next

If groupToFilter = "All" Then
		xp = "group"
	Else
		xp = "group[@name='" & groupToFilter & "']"
	End If

Set nodeList = xmlDoc.documentElement.selectNodes(xp & "/server") 
	n = 1
	For Each node In nodeList
		ReDim Preserve m_server_name(n)
		ReDim Preserve m_server_desc(n)
		ReDim Preserve m_server_domain(n)
		m_server_name(n) = node.attributes.GetNamedItem("name").NodeValue
		m_server_desc(n) = node.attributes.GetNamedItem("desc").NodeValue
		m_server_domain(n) = node.attributes.GetNamedItem("domain").NodeValue
		n = n + 1
	Next

Set nodeList = xmlDoc.documentElement.selectNodes(xp & "/instance") 
	n = 1
	For Each node In nodeList
		ReDim Preserve m_instance_name(n)
		ReDim Preserve m_instance_desc(n)
		ReDim Preserve m_instance_auth(n)
		ReDim Preserve m_instance_uid(n)
		ReDim Preserve m_instance_pwd(n)
		m_instance_name(n) = node.attributes.GetNamedItem("name").NodeValue
		m_instance_desc(n) = node.attributes.GetNamedItem("desc").NodeValue
		m_instance_auth(n) = node.attributes.GetNamedItem("auth").NodeValue
		If m_instance_auth(n) = "sql" Then
			m_instance_uid(n) = node.attributes.GetNamedItem("uid").NodeValue
			m_instance_pwd(n) = node.attributes.GetNamedItem("pwd").NodeValue
		End If
		n = n + 1
	Next

m_filterGroup = groupToFilter
	DisplayServers
End Sub

Sub DisplayServers()
	Dim row, cell
	For i = 1 To UBound(m_server_name, 1)
		Set row = tblServerSettings.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = m_server_desc(i)
		Set cell = row.insertCell()
		cell.innerHtml = "<input type='checkbox' id='server_" & CStr(i) & "_SC'></input>"
	Next

For i = 1 To UBound(m_instance_name, 1)
		Set row = tblInstanceSettings.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = m_instance_desc(i)
		Set cell = row.insertCell()
		cell.innerHtml = "<input type='checkbox' id='instance_" & CStr(i) & "_IS'></input>"
	Next
End Sub

Sub GetStats()

If txtPwd.value = "" Then
		MsgBox "Enter a valid password"
		Exit Sub
	End If

document.all("settings").style.display = "none"
	document.all("results").style.display = "block"

m_timerId = setInterval("fsm_Control", 5)
End Sub

Sub fsm_Control()
	If m_timerId > 0 Then
		clearInterval m_timerId
	End If

Select Case m_state
	Case NONE
		m_state = SERVER_CONFIG
		m_index = 1
		m_timerId = setInterval("fsm_Control", 5)
		Exit Sub
	Case SERVER_CONFIG
		If document.all("server_" & CStr(m_index) & "_SC").checked Then
			txtStatus.innerHtml = "Retrieved Configuration for " & m_server_desc(m_index)
			getServerSummary m_server_name(m_index), m_server_desc(m_index), m_server_domain(m_index)
		End If
		m_index = m_index + 1
		If m_index > UBound(m_server_name, 1) Then
			m_index = 1
			m_state = INSTANCE_SUMMARY
		End If
		m_timerId = setInterval("fsm_Control", 5)
	Case INSTANCE_SUMMARY
		If document.all("instance_" & CStr(m_index) & "_IS").checked Then
			txtStatus.innerHtml = "Retrieved Configuration for " & m_instance_desc(m_index)
			getInstanceSummary m_instance_name(m_index), m_instance_desc(m_index), m_instance_auth(m_index), m_instance_uid(m_index), m_instance_pwd(m_index)
		End If
		m_index = m_index + 1
		If m_index > UBound(m_instance_name, 1) Then
			m_index = 1
			m_state = NONE
			txtStatus.innerHtml = "SQL Server Audit at " & CStr(Now()) & "<br/>Servers: " & CStr(UBound(m_server_name, 1)) & "  Instances: " & CStr(UBound(m_instance_name, 1))
		Else
			m_timerId = setInterval("fsm_Control", 5)
		End If
	End Select

End Sub

Sub toggleCheckboxes(pre, post)
	dim i
	i = 1
	On Error Resume Next
	While True
		v = document.all(pre & "_" & CStr(i) & "_" & post).checked
		If Err <> 0 Then
			Exit Sub
		End If

v = Not v
		document.all(pre & "_" & CStr(i) & "_" & post).checked = v
		i = i + 1
	Wend
End Sub

Sub getServerSummary(svr_ip, svr_name, domain)
	On Error Resume Next

username = txtAccount.value
	password = txtPwd.value

Set objPassword = CreateObject("ScriptPW.Password")
	strPassword = password
 
	Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator")
	Set objSWbemServices = objSWbemLocator.ConnectServer(svr_ip, "root\cimv2", userName, strPassword, "MS_409", "ntlmdomain:" + domain)
	If Err <> 0 Then
		AddError "Can't get server configuration for " & svr_name
		Exit Sub
	End If

If document.all("serverConfig").innerHtml = "" Then
		document.all("serverConfig").innerHtml = "<h2>Server Configuration</h2>"
	End If

Set tbl = document.createElement("TABLE")
	document.all("serverConfig").appendChild(tbl)
	Set row = tbl.insertRow()
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.style.width = 200
	cell.innerHtml = "Server"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Status"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Domain"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Model"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Memory (MB)"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "# CPUs"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Startup Options"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Operating System"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Service Pack"

Set colSwbemObjectSet = objSWbemServices.ExecQuery("Select * From Win32_ComputerSystem")
	For Each objComputer in colSwbemObjectSet
		Set row = tbl.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = svr_name
		Set cell = row.insertCell()
		cell.innerHtml = objComputer.Status
		If objComputer.Status <> "OK" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = objComputer.Domain
		Set cell = row.insertCell()
		cell.innerHtml = objComputer.Manufacturer & " " & objComputer.Model
		Set cell = row.insertCell()
		cell.innerHtml = CInt(objComputer.TotalPhysicalMemory / 1024 / 1024)
		Set cell = row.insertCell()
		cell.innerHtml = objComputer.NumberOfProcessors
		Set cell = row.insertCell()
		ss = objComputer.SystemStartupOptions(objComputer.SystemStartupSetting)
		If Instr(ss, "/") > 0 Then
			cell.innerHtml = Mid(ss, Instr(ss, "/"))
		Else
			cell.innerHtml = ""
		End If
	Next
	Set colSwbemObjectSet = objSWbemServices.ExecQuery("Select * From Win32_OperatingSystem")
	For Each objOperatingSystem in colSWbemObjectSet
		Set cell = row.insertCell()
		cell.innerHtml = Left(objOperatingSystem.Name, Instr(objOperatingSystem.Name, "|") - 1)
		Set cell = row.insertCell()
		cell.innerHtml = objOperatingSystem.ServicePackMajorVersion & "." & objOperatingSystem.ServicePackMinorVersion
	Next

Set tbl = document.createElement("TABLE")
	document.all("serverConfig").appendChild(tbl)
	Set row = tbl.insertRow()
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.style.width = 250
	cell.innerHtml = "Processor"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.style.width = 250
	cell.innerHtml = "Description"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Width (bits)"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Max Speed (MHz)"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Current Speed (MHz)"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Load %"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Status"

Set colSwbemObjectSet = objSWbemServices.ExecQuery("Select * From Win32_Processor")
	For Each objProcessor in colSWbemObjectSet
		Set row = tbl.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.Name
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.Description
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.AddressWidth
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.MaxClockSpeed
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.CurrentClockSpeed
		If objProcessor.CurrentClockSpeed <> objProcessor.MaxClockSpeed Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.LoadPercentage
		If objProcessor.LoadPercentage >= 90 Then
			cell.className = "alert"
		ElseIf objProcessor.LoadPercentage >= 50 Then
			cell.className = "warning"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = objProcessor.Status
		If objProcessor.Status <> "OK" Then
			cell.className = "alert"
		End If
	Next

Set row = tbl.insertRow()
	Set cell = row.insertCell()
	cell.innerHtml = " "
End Sub

Function buildConnectionString(name, auth, uid, pwd)
	If auth = "win" Then
		buildConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=" & name & ";INITIAL CATALOG=master;INTEGRATED SECURITY=SSPI;CONNECT TIMEOUT=30;"
	Else
		buildConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=" & name & ";INITIAL CATALOG=master;uid=" & uid & ";pwd=" & pwd & ";CONNECT TIMEOUT=30;"
	End If
End Function

Sub getInstanceSummary(name, desc, auth, uid, pwd)
	On Error Resume Next
	pcn = buildConnectionString(name, auth, uid, pwd)
	Set cn = CreateObject("ADODB.Connection")
	Set rs = CreateObject("ADODB.Recordset")
	cn.Open pcn
	If Err <> 0 Then
		AddError "Can't connect to " & desc
		Exit Sub
	End If

If document.all("instanceConfig").style.display <> "block" Then
		document.all("instanceConfig").style.display = "block"
	End If

sql = "set nocount on; "
	sql = sql & "create table #t (name nvarchar(255), minimum int, maximum int, config_value int, run_value int); "
	sql = sql & "insert #t exec sp_configure; "
	sql = sql & "select substring(@@version, 1, charindex('-', @@version) - 2) sql_version,"
	sql = sql & "SERVERPROPERTY('Edition') as sql_Edition,"
	sql = sql & "SERVERPROPERTY('ProductLevel') as sql_Level,"
	sql = sql & "SERVERPROPERTY('ProductVersion') as ProductVersion,"
	sql = sql & "SERVERPROPERTY('Collation') as Collation,"
	sql = sql & "SERVERPROPERTY('IsClustered') IsClustered,"
	sql = sql & "SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled,"
	sql = sql & "SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly,"
	sql = sql & "SERVERPROPERTY('IsSingleUser') IsSingleUser,"
	sql = sql & "SERVERPROPERTY('ProcessID') as ProcessId,"
	sql = sql & "	(select run_value from #t where name = 'affinity mask') affinity_mask,"
	sql = sql & "	(select run_value from #t where name = 'awe enabled') is_awe_enabled, "
	sql = sql & "	(select run_value from #t where name like 'min server memory%') min_server_memory,"
	sql = sql & "	(select run_value from #t where name like 'max server memory%') max_server_memory,"
	sql = sql & "	(select run_value from #t where name = 'lightweight pooling') lightweight_pooling,"
	sql = sql & "	(select run_value from #t where name = 'max degree of parallelism') max_degree_of_parallelism,"
	sql = sql & "	(select run_value from #t where name = 'priority boost') priority_boost;"
	sql = sql & "drop table #t;"

rs.ActiveConnection = cn
	rs.Open sql

If Err <> 0 Then
		AddError "SQL Error getting Instance Configuration for " & desc
		Exit Sub
	End If

While Not rs.EOF
		Dim row, cell
		Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Instance"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "SQL Server Version"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = ""
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = ""
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Process Id"

Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = desc
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("sql_version")
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("sql_edition")
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("sql_level") & " (" & rs.Fields("ProductVersion") & ")"
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("ProcessId")

Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = ""
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Min/Max Server Memory"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "AWE Enabled"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Clustered"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Single User"

Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = ""
		Set cell = row.insertCell()
		cell.innerHtml = nullNull(rs.Fields("min_server_memory")) & "/" & nullNull(rs.Fields("max_server_memory"))
		Set cell = row.insertCell()
		cell.innerHtml = yesNo(rs.Fields("is_awe_enabled"))
		Set cell = row.insertCell()
		cell.innerHtml = yesNo(rs.Fields("IsClustered"))
		Set cell = row.insertCell()
		cell.innerHtml = yesNo(rs.Fields("IsSingleUser"))
		If rs.Fields("IsSingleUser") = 1 Then
			cell.className = "alert"
		End If

Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = ""
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Collation"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Affinity Mask"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Lightweight Pooling"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Max Deg. Parallelism"
		Set cell = row.insertCell()
		cell.className = "headings"
		cell.innerHtml = "Priority Boost"

Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = ""
		Set cell = row.insertCell()
		cell.innerHtml = nullNull(rs.Fields("Collation"))
		Set cell = row.insertCell()
		cell.innerHtml = nullNull(rs.Fields("affinity_mask"))
		If rs.Fields("affinity_mask") <> 0 Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = yesNo(rs.Fields("lightweight_pooling"))
		If rs.Fields("lightweight_pooling") = 1 Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = nullNull(rs.Fields("max_degree_of_parallelism"))
		If rs.Fields("max_degree_of_parallelism") <> 0 Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = yesNo(rs.Fields("priority_boost"))
		If rs.Fields("priority_boost") = 1 Then
			cell.className = "alert"
		End If

rs.MoveNext
	Wend

Set rs = CreateObject("ADODB.Recordset")

sql = "set nocount on; "
	sql = sql & "declare @name varchar(255), @sql varchar(1000);"
	sql = sql & "if @@version like '%9.00.%' "
	sql = sql & "begin "

sql = sql & "	select name, DATABASEPROPERTYEX(name, 'Status') as Status,"
	sql = sql & "		case when DATABASEPROPERTYEX(name, 'IsAutoCreateStatistics') = 1 then 'ON' else 'OFF' end as AutoCreateStatistics, "
	sql = sql & "		case when DATABASEPROPERTYEX(name, 'IsAutoUpdateStatistics') = 1 then 'ON' else 'OFF' end as AutoUpdateStatistics, "
	sql = sql & "		case when DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1 then 'ON' else 'OFF' end as AutoShrink, "
	sql = sql & "		DATABASEPROPERTYEX(name, 'Collation') as Collation, "
	sql = sql & "		DATABASEPROPERTYEX(name, 'Updateability') as Updateability, "
	sql = sql & "		DATABASEPROPERTYEX(name, 'UserAccess')  as UserAccess, "
	sql = sql & "		page_verify_option_desc as PageVerifyOption, "
	sql = sql & "		compatibility_level as CompatibilityLevel, "
	sql = sql & "		DATABASEPROPERTYEX(name, 'Recovery')  as RecoveryModel, convert(bigint, 0) as Size "
	sql = sql & "	into #dbs1 "
	sql = sql & "	from master.sys.databases; "
	sql = sql & "create table #dbsize1 (dbname varchar(255), Size bigint);"
	sql = sql & "declare c1 cursor for select name from #dbs1;"
	sql = sql & "open c1;"
	sql = sql & "fetch next from c1 into @name;"
	sql = sql & "while @@fetch_status = 0 "
	sql = sql & "begin "
	sql = sql & "set @sql = 'select ''' + @name + ''', sum(size) from [' + @name + ']..sysfiles'; "
	sql = sql & "insert #dbsize1 exec(@sql); "
	sql = sql & "update #dbs1 "
	sql = sql & "set Size = s.Size * 8 / 1024 "
	sql = sql & "from #dbs1 d "
	sql = sql & "join #dbsize1 s on d.name = s.dbname; "
	sql = sql & "truncate table #dbsize1; "
	sql = sql & "fetch next from c1 into @name; "
	sql = sql & "end; "
	sql = sql & "close c1; "
	sql = sql & "deallocate c1; "
	sql = sql & "select * from #dbs1 "
	sql = sql & "order by name; "
	sql = sql & "drop table #dbsize1; "
	sql = sql & "drop table #dbs1; "
	sql = sql & "end "
	sql = sql & "else "
	sql = sql & "begin "

sql = sql & "select name, DATABASEPROPERTYEX(name, 'Status') as Status,"
	sql = sql & "case when DATABASEPROPERTYEX(name, 'IsAutoCreateStatistics') = 1 then 'ON' else 'OFF' end as AutoCreateStatistics, "
	sql = sql & "case when DATABASEPROPERTYEX(name, 'IsAutoUpdateStatistics') = 1 then 'ON' else 'OFF' end as AutoUpdateStatistics, "
	sql = sql & "case when DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1 then 'ON' else 'OFF' end as AutoShrink, "
	sql = sql & "DATABASEPROPERTYEX(name, 'Collation') as Collation, "
	sql = sql & "DATABASEPROPERTYEX(name, 'Updateability') as Updateability, "
	sql = sql & "DATABASEPROPERTYEX(name, 'UserAccess')  as UserAccess, "
	sql = sql & "case when DATABASEPROPERTYEX(name, 'IsTornPageDetectionEnabled') = 1 then 'TORN_PAGE_DETECTION' else 'NONE' end as PageVerifyOption, "
	sql = sql & "cmptlevel as CompatibilityLevel, "
	sql = sql & "DATABASEPROPERTYEX(name, 'Recovery')  as RecoveryModel, convert(bigint, 0) as Size "
	sql = sql & "into #dbs "
	sql = sql & "from master..sysdatabases; "
	sql = sql & "create table #dbsize (dbname varchar(255), Size bigint);"
	sql = sql & "declare c cursor for select name from #dbs;"
	sql = sql & "open c;"
	sql = sql & "fetch next from c into @name;"
	sql = sql & "while @@fetch_status = 0 "
	sql = sql & "begin "
	sql = sql & "set @sql = 'select ''' + @name + ''', sum(size) from [' + @name + ']..sysfiles'; "
	sql = sql & "insert #dbsize exec(@sql) "
	sql = sql & "update #dbs "
	sql = sql & "set Size = s.Size * 8 / 1024 "
	sql = sql & "from #dbs d "
	sql = sql & "join #dbsize s on d.name = s.dbname; "
	sql = sql & "truncate table #dbsize; "
	sql = sql & "fetch next from c into @name; "
	sql = sql & "end; "
	sql = sql & "close c; "
	sql = sql & "deallocate c; "
	sql = sql & "select * from #dbs "
	sql = sql & "order by name; "
	sql = sql & "drop table #dbsize; "
	sql = sql & "drop table #dbs; "

sql = sql & "end;"

rs.ActiveConnection = cn
	rs.Open sql

If Err <> 0 Then
		AddError "SQL Error getting Database Configuration for " & desc
		Exit Sub
	End If

Set row = tblInstanceSummary.insertRow()
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Database"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Collation"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Page Verify Option"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Updateability"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "User Access"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Status"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Auto Shrink"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Cmpt Level"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Recovery Model"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Auto Create Stats"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Auto Update Stats"
	Set cell = row.insertCell()
	cell.className = "headings"
	cell.innerHtml = "Size (MB)"

While Not rs.EOF
		Set row = tblInstanceSummary.insertRow()
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("name")
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("Collation")
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("PageVerifyOption")
		If rs.Fields("PageVerifyOption") = "NONE" And rs.Fields("name") <> "tempdb" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("Updateability")
		If rs.Fields("Updateability") <> "READ_WRITE" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("UserAccess")
		If rs.Fields("UserAccess") <> "MULTI_USER" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("Status")
		If rs.Fields("Status") <> "ONLINE" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("AutoShrink")
		If rs.Fields("AutoShrink") = "ON" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("CompatibilityLevel")
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("RecoveryModel")
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("AutoCreateStatistics")
		If rs.Fields("AutoCreateStatistics") = "OFF" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("AutoUpdateStatistics")
		If rs.Fields("AutoUpdateStatistics") = "OFF" Then
			cell.className = "alert"
		End If
		Set cell = row.insertCell()
		cell.innerHtml = rs.Fields("Size")

rs.MoveNext
	Wend
	Set row = tblInstanceSummary.insertRow()
	Set cell = row.insertCell()
	cell.innerHtml = " "
End Sub

Function yesNo(bit)
	If bit = 1 Then
		yesNo = "Yes"
	Else
		yesNo = "No"
	End If
End Function

Function nullNull(f)
	If IsNull(f) Then
		nullNull = "NULL"
	Else
		nullNull = f
	End If
End Function

Sub AddError(errMsg)
	If document.all("errors").style.display <> "block" Then
		document.all("errors").style.display = "block"
	End If

Dim row, cell
	Set row = tblErrors.insertRow()
	row.className = "alert"
	Set cell = row.insertCell()
	cell.innerHtml = errMsg
End Sub

</script>
<body>
<div id="settings">
Admin Account <input type="text" id="txtAccount" value="administrator" />
 Password <input type="password" id="txtPwd" />
 <select id="cboGroup" onchange="cboGroup_onchange"></select>
 <input type="button" onclick="GetStats" value="Run" /><br/><br/>
<table><tr><td valign="top">
<table id="tblServerSettings"><tr class="headings">
	<td>Server</td>
	<td><a href="#" onclick='toggleCheckboxes "server", "SC"'>Configuration</a></td></tr>
</tr>
</table>
</td><td valign="top">
<table id="tblInstanceSettings"><tr class="headings">
	<td>Instance</td>
	<td><a href="#" onclick='toggleCheckboxes "instance", "IS"'>Configuration</a></td></tr>
</tr>
</table>
</td></tr></table>
</div>
<div id="results" style="display:none">
<div id="txtStatus"></div><br/>
<table><tr><td valign="top" align="left">
<div id="serverConfig"></div>
</td></tr>
<tr><td valign="top" align="left">
<div id="instanceConfig" style="display:none">
<h2>Instance Configuration</h2>
<table id="tblInstanceSummary">
</table>
<br/><br/>
</div>
<div id="errors" style="display:none">
<h2>Errors</h2>
<table id="tblErrors">
</table>
</div>
</td></tr></table>
</div>
</body>
</html>

Now create a file called SQL Servers.xml and save it to the same folder, open it with notepad and paste the following xml into it:

This should be fairly self-explanatory. The name attributes may contain a server name or ip address. The desc attributes contain what is displayed to the user. Windows and SQL Server authentication are both supported, and I have shown examples of each. The grouping is useful in a large environment. You can have as many groups as you like.

Replace my example servers and instances with ones from your own environment and save the file. Now double-click on SQL Audit.hta to run the script.

The script uses WMI to retrieve the server configuration. Please be aware that there can be a problem with WMI on some servers, causing it to hang indefinitely. The offending server will have to be removed from the xml file when this happens, unless you know of a fix for the problem (in which case please contact me).

When I make future changes to this SQL Server audit script I will describe them in a new article, so be sure to subscribe to my RSS feed.

SQL Server Audit Script - Part 2 -> SQL Server Scripts

SQL Server Audit Script - Part 2 -> SQL Server Pro Home

SQL Server Audit Script - Part 2

Navigation