Featured Database Articles

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.

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]

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]