Query for SQL audit

Hey everyone,

This is my first question here for a long while, so be extra nice ;)

I am doing an audit on some of my sql server 2000s and I would to know if its possible to automate the collection of some of the data.

Firstly I would like to be able to query the domain account that SQLSERVER and SQLAGENT are running on, in my case the agent and service account will alaways be the same, but the account name maybe different depending on what server it is.

Secondly I would like to be able to query whether the account SQL server is running on is local admin of the server...I know some of you will say the SQL account has to be local admin but with the right registry and folder level permissions it is not necessary for the account to be local admin. This was a requirement from a very strict security audit.You can probably find the service accounts using some of the undocumented extended stored procedures that Enterprise Manager uses. Set up a profiler trace and then use EM to get that information. Inspect the trace for the commands that EM used to get the service accounts, and you should be set there.

As for the second, you may need to resort to WMI (Windows Management Instrumentation), which is usable through VB script. I am not good enough to tell you how to get started, beyond a google search, though.|||As for the second, you may need to resort to WMI (Windows Management Instrumentation), which is usable through VB script. I am not good enough to tell you how to get started, beyond a google search, though.

You can use WMI to grab both pieces of information:

Members of Local Admins Group

Set oItems = Nothing

' Next grab the members of the Local Administrators Group
Set oItems = oWMIService.ExecQuery( "SELECT PartComponent FROM Win32_GroupUser WHERE GroupComponent = ""Win32_Group.Domain='" & sComputer & "',Name='Administrators'""", _
"WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly )

For Each oItem in oItems
Set oMember = oWMIService.Get(oItem.PartComponent)

Set oComm = CreateObject("ADODB.Command")
oComm.CommandText = "spUpdateServerPermissions"
oComm.CommandType = 4
oComm.ActiveConnection = oConn

oComm.Parameters.Refresh
oComm.Parameters("@.SERVER") = sComputer
oComm.Parameters("@.GROUPNAME") = "Administrators"
oComm.Parameters("@.MEMBERNAME") = oMember.Caption

oComm.Execute
Set oComm = Nothing
Next

To grab information related to services running:

Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security )}!\\" & sFQDN & "\root\cimv2")
Set oItems = oWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

Set oComm = CreateObject("ADODB.Command")

' Open connection to catalog database
oConn.ConnectionString = sConnectionString
oConn.Open

If Err.number = 0 Then
For Each oItem in oItems
If IsNull(oItem.DisplayName) Then
sDisplayName = oItem.Name
Else
sDisplayName = oItem.DisplayName
End If

' Bunch o' code to load results into db...
Next


The snippets are not complete; you need to dim the objects and define sFQDN, but you should be able to make some sense of them...

Regards,

hmscott|||Thanks very much guys :) that looks perfect.