Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 20, 2008

Audit your Windows domain DBA group using PowerShell

By Muthusamy Anantha Kumar aka The MAK

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.

[string] $domainname,
[string] $dbagroupname,
[string] $smtpServer,
[string] $from,
[string] $to

$s=Test-Path $filenamey
if($s.tostring() -eq "False")
$group = [ADSI]($g)
$ulist = $group.psbase.invoke("Members") |foreach{$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}
out-file -inputobject  $ulist -filepath $filenamet -encoding "Default"
$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"
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.Subject = "Changes in " + $domainname+"\"+$dbagroupname
$msg.Body = "Please find attached files for details"

Step 4

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

./audit_domain_group.ps1 PowerPc sqldba Mymailserver

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, 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]


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

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


PS C:\PowerSQL> Get-content  PowerPc_sqldba_2008_8_14.txt
PS C:\PowerSQL> Get-content  PowerPc_sqldba_2008_8_13.txt
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.


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

» See All Articles by Columnist MAK

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

Thanks for your registration, follow us on our social networks to keep up-to-date