Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by MCrowley
    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
    Code:
    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:
    Code:
    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
    Last edited by hmscott; 11-30-07 at 14:44.
    Have you hugged your backup today?

  4. #4
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Thanks very much guys that looks perfect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •