Audit your Windows domain DBA group using PowerShell

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]

Fig 1.0

Step 2

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



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



Fig 1.2

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]



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



Fig 1.4

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]



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]



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



Fig 1.7

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]



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>



Fig 1.9

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



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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles