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 SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


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

Featured Database Articles

MS SQL

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.

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



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