Audit your Windows domain DBA group using PowerShell

August 20, 2008

In many organizations, the Active Directory administrator creates a dedicated domain group for Database administrators. All of the domain logins of the Database Administrator logins are added to that dedicated domain group.

These dedicated domain groups are usually added as local administrator on the SQL Server host as well as being granted access to SQL Server with sysadmin role.

It is easy to add or remove a login from the domain group instead of adding individual DBA logins to every SQL Server box. However, it is vital to know what logins are added to the domain group and which logins are being deleted. This article illustrates how to audit such changes on the domain group.

Let us assume that “PowerPc” is the domain and “sqldba” is the dedicated group for SQL Server database administrators.

Step 1

Create c:\powersql folder as shown below. [Refer Figure 1.0]

Step 2

Create c:\PowerSQL\audit_domain_group.ps1 as shown below. [Refer Fig 1.1]

Step 3

Copy and paste the code below and save the file.

param
(
[string] $domainname,
[string] $dbagroupname,
[string] $smtpServer,
[string] $from,
[string] $to
)
$Today=get-date
$filenamet="C:\PowerSQL\"+$domainname+"_"+$dbagroupname+"_"+($Today.year).ToString()+"_"
$filenamet=$filenamet+($Today.month).ToString()+"_"
$filenamet=$filenamet+($Today.day).ToString()+".txt"
$Yesterday=(get-date).AddDays(-1)
$filenamey="C:\PowerSQL\"+$domainname+"_"+$dbagroupname+"_"+($Yesterday.year).ToString()+"_"
$filenamey=$filenamey+($Yesterday.month).ToString()+"_"
$filenamey=$filenamey+($Yesterday.day).ToString()+".txt"
$changes=get-date
$filenamec="C:\PowerSQL\"+$domainname+"_"+$dbagroupname+"_changes_"+($changes.year).ToString()+"_"
$filenamec=$filenamec+($changes.month).ToString()+"_"
$filenamec=$filenamec+($changes.day).ToString()+".txt"

$s=Test-Path $filenamey
if($s.tostring() -eq "False")
{
$g="WinNT://"+$domainname+"/"+$dbagroupname+",group"
$group = [ADSI]($g)
$ulist = $group.psbase.invoke("Members") |foreach{$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}
out-file -inputobject  $ulist -filepath $filenamet -encoding "Default"
}
else
{
$g="WinNT://"+$domainname+"/"+$dbagroupname+",group"
$group = [ADSI]($g)
$ulist = $group.psbase.invoke("Members") |foreach{$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}
out-file -inputobject  $ulist -filepath $filenamet -encoding "Default"
$yesterday=get-content $filenamey
$today=get-content $filenamet
$compared=Compare-Object $today $yesterday
if ($compared -eq $NULL)
{
write-host "No Change"
}
else
{
write-host "Changes are present"
#write-host $compared
#$compared | gm

out-file -inputobject "The following changes are made to the DBA group" -filepath $filenamec -encoding "Default"
out-file -inputobject "------------------------------------------------" -filepath $filenamec -encoding "Default" -append
foreach ($names in $compared)
{
if ($names.SideIndicator -eq "<=")
{
$out="Member(s) Added to " +$domainname+"\"+$dbagroupname +"-->" + $names.InputObject
out-file -inputobject $out -filepath $filenamec -encoding "Default" -append
}
if ($names.SideIndicator -eq "=>")
{
$out="Member(s) Deleted from "+ $domainname+"\"+$dbagroupname+ " -->" +  $names.InputObject
out-file -inputobject $out -filepath $filenamec -encoding "Default" -append
}

}


}

}

if ($compared -ne $NULL)
{


$msg = new-object Net.Mail.MailMessage
$att1 = new-object Net.Mail.Attachment($filenamey)
$att2 = new-object Net.Mail.Attachment($filenamet)
$att3 = new-object Net.Mail.Attachment($filenamec)
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$msg.From = $from
$msg.To.Add($to)
$msg.Subject = "Changes in " + $domainname+"\"+$dbagroupname
$msg.Body = "Please find attached files for details"
$msg.Attachments.Add($att1) 
$msg.Attachments.Add($att2) 
$msg.Attachments.Add($att3) 
$smtp.Send($msg)
}

Step 4

Execute the script from the PowerShell command window as shown below. [Refer Fig 1.2]

./audit_domain_group.ps1 PowerPc sqldba Mymailserver youremail@youremailserver.com
 youremail@youremailserver.com

Note: the parameters PowerPC is the domain name, sqldba is the domain group for database administrators, MyMailserver is the smtp mail server, youremail@youremailserver.com is the from email address and to email address.

Please update all the parameters according to your environment.

The first time this script is executed, it creates the file “PowerPc_sqldba_2008_8_13.txt” as shown below. [Refer Fig 1.3]

The content of the file is the list of all of the members in that particular domain group. You can execute the following PowerShell cmdlet to see the content of the file.

get-content PowerPc_sqldba_2008_8_13.txt

The result is shown below. [Refer Fig 1.4]

sqldba3
sqldba2
sqldba1
sqldba8
sqldba7
sqldba5
sqldba41
sqldba4

Step 5

When this script is executed the next day, it creates “PowerPc_sqldba_2008_8_14.txt” and compares the content of both files. If there is a change in the content, it will email.

In order to test this, I am going to make a couple of changes. First, I am going change the system date to the next day. [Fig 1.5]

In addition, I am going to remove sqldba1 and sqldba2 from the domain group sqldba as shown below. Then I am going to add a new user “sqldba100” to the group. [Refer Fig 1.6]

Note: The actual screen of adding and removing users from the domain differs and depends on the version of windows you use. Here I am using a local group on my PC.

Step 6

Now let us execute the script from the PowerShell command window as shown below. [Refer Fig 1.7]

./audit_domain_group.ps1 PowerPc sqldba Mymailserver youremail@youremailserver.com  youremail@youremailserver.com

This will create the file “PowerPc_sqldba_2008_8_14.txt” and since we made changes to the domain group, it also creates the file “PowerPc_sqldba_changes_2008_8_14.txt” as shown below. [Fig 1.8]

The content of the file “PowerPc_sqldba_2008_8_14.txt” is the list of logins in that particular group today. The content of the file “PowerPc_sqldba_changes_2008_8_14.txt” is the comparison between the file “PowerPc_sqldba_2008_8_14.txt” and file “PowerPc_sqldba_2008_8_13.txt”. To see the content of the file, execute the following Powershell cmdlet. [Refer Fig 1.9]

Get-content  PowerPc_sqldba_2008_8_14.txt
Get-content  PowerPc_sqldba_2008_8_13.txt
Get-content  PowerPc_sqldba_changes_2008_8_14.txt

Result

PS C:\PowerSQL> Get-content  PowerPc_sqldba_2008_8_14.txt
sqldba3
sqldba8
sqldba7
sqldba5
sqldba41
sqldba4
sqldba100
PS C:\PowerSQL> Get-content  PowerPc_sqldba_2008_8_13.txt
sqldba3
sqldba2
sqldba1
sqldba8
sqldba7
sqldba5
sqldba41
sqldba4
PS C:\PowerSQL> Get-content  PowerPc_sqldba_changes_2008_8_14.txt
The following changes are made to the DBA group
------------------------------------------------
Member(s) Deleted from PowerPc\sqldba -->Member(s) Deleted from PowerPc\sqldba -->sqldba1
Member(s) Added to PowerPc\sqldba-->sqldba100
PS C:\PowerSQL>

This script also sends out an email as shown below. [Refer Fig 2.0]

Schedule this script in windows scheduler to be notified whenever there is a change in the domain group.

Conclusion

This article illustrated how to audit changes on the domain group dedicated for SQL Server database administrators.

» See All Articles by Columnist MAK