SQL Server Audit with Powershell Excel Automation

Here’s neat little Powershell script you can use to audit your SQL Server databases. The script is dependant on the SQL Browser service, to discover instances, so you will need to make sure this is running. This will allow you to audit all SQL Server instances on the localhost with details of your databases and associated information. I use SMO here so it should be pretty easy to customise for your own purposes. Just run the script and a nicely formatted Excel report of your databases will be produced. Enjoy!

?View Code POWERSHELL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$smoObj = [Microsoft.SqlServer.Management.Smo.SmoApplication];
 
# This gets the sql server
$sql = $smoObj::EnumAvailableSqlServers($false);
 
# Automate Excel
$xl = New-Object -ComObject Excel.Application;
$xl.Visible = $true;
$xl = $xl.Workbooks.Add();
$Sheet = $xl.Worksheets.Item(1);
 
$row = 1;
 
foreach($sqlserver in $sql)
{
	 # headers
     $Sheet.Cells.Item($row, 1) = "Sql Server:";
     $Sheet.Cells.Item($row, 2) = $sqlserver.Name;
     $Sheet.Cells.Item($row, 1).Font.Bold = $true;
     $Sheet.Cells.Item($row, 2).Font.Bold = $true;
	$Sheet.Cells.Item($row, 3) = "";
     	$Sheet.Cells.Item($row, 4) = "Instance:";
     	$Sheet.Cells.Item($row, 5) = $sqlserver.Instance;
     	$Sheet.Cells.Item($row, 4).Font.Bold = $true;
     	$Sheet.Cells.Item($row, 5).Font.Bold = $true;
	 $Sheet.Cells.Item($row, 6) = "";
	 $Sheet.Cells.Item($row, 7) = "Version: ";
	 $Sheet.Cells.Item($row, 8) = $sqlserver.Version;
     	$Sheet.Cells.Item($row, 7).Font.Bold = $true;
     	$Sheet.Cells.Item($row, 8).Font.Bold = $true;
 
	 # Prettify headers
	 for($i = 1; $i -le 8; $i++)
	 {
	 	$Sheet.Cells.Item($row,$i).Interior.ColorIndex = 50;
     		$Sheet.Cells.Item($row,$i).Font.ColorIndex = 20;
	 }
 
	 # Create obj for this sql server
	 $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver.Name;
	 # Get the databases on this sql server
	 $databases = $srv.Databases;
 
	 # Increase rowcount for formatting
	 $row += 2;
 
	 # Add column headers for databases
	 $Sheet.Cells.Item($row, 1) = "Database";
	 $Sheet.Cells.Item($row, 2) = "Size";
	 $Sheet.Cells.Item($row, 3) = "SpaceAvailable";
	 $Sheet.Cells.Item($row, 4) = "State";
	 $Sheet.Cells.Item($row, 5) = "Table Count";
	 $Sheet.Cells.Item($row, 6) = "Collation";
	 $Sheet.Cells.Item($row, 7) = "Compatibility Level";
	 $Sheet.Cells.Item($row, 8) = "Create Date";
	 $Sheet.Cells.Item($row, 9) = "Index Space Usage";
	 $Sheet.Cells.Item($row, 10) = "Owner";
	 $Sheet.Cells.Item($row, 11) = "Last Backup";
	 $Sheet.Cells.Item($row, 12) = "Trigger Count";
	 $Sheet.Cells.Item($row, 13) = "UDF Count";
	 for($i = 1; $i -le 13; $i++)
	 {
	 	$Sheet.Cells.Item($row,$i).Interior.ColorIndex = 35;
     		$Sheet.Cells.Item($row,$i).Font.ColorIndex = 0;
		$Sheet.Cells.Item($row, $i).Font.Bold = $true;
	 }
	 $row++;
	 # Work through each database in the collection
	 foreach($db in $databases)
	 {
	 	$Sheet.Cells.Item($row, 1) = $db.Name;
		$Sheet.Cells.Item($row, 2) = $db.Size;
		$Sheet.Cells.Item($row, 3) = $db.SpaceAvailable;
		$Sheet.Cells.Item($row, 4) = $db.State;
		$Sheet.Cells.Item($row, 5) = $db.Tables.Count;
		$Sheet.Cells.Item($row, 6) = $db.Collation;
		$Sheet.Cells.Item($row, 7) = $db.CompatibilityLevel;
		$Sheet.Cells.Item($row, 8) = $db.CreateDate;
		$Sheet.Cells.Item($row, 9) = $db.IndexSpaceUsage;
		$Sheet.Cells.Item($row, 10) = $db.Owner;
		$Sheet.Cells.Item($row, 11) = $db.LastBackupDate;
		$Sheet.Cells.Item($row, 12) = $db.Triggers.Count;
		$Sheet.Cells.Item($row, 13) = $db.UserDefinedFunctions.Count;
		for($i = 1; $i -le 13; $i++)
		{
	 		$Sheet.Cells.Item($row,$i).Interior.ColorIndex = 0;
     			$Sheet.Cells.Item($row,$i).Font.ColorIndex = 0;			
		}
		$row++;
	 }
 
	 $row++;
}
 
# Apply autoformat
$Sheet.UsedRange.EntireColumn.AutoFit();

powershell excel sql server database audit


3 Comments

  1. Dendrite says:

    I was just about to burn up a Saturday trying to write this. Thanks, Rhys!

  2. Could we use script for remote machines and how ??

    Thanks

  3. Rhys says:

    Yes you could, I need to update this script to correct a few issues, This script will discover remote machines if the sql browser service is running. Otherwise you could modify it to point it at known hosts. You can modify the script basing it on this – http://www.youdidwhatwithtsql.com/check-database-autoshrink-setting-powershell/1089

    Cheers,

    Rhys

Leave a Reply