SQL Server PowerShell : How to Audit Your SQL Server Instance Properties Using PowerShell and SMO

Written by belle
Posted June 3, 2009 at 12:11 am

Here is a simple script to audit your SQL Server Instance Properties.

Note that the property names are not hardcoded. We query each of these properties, and use those to display the property values.

If you prefer, you can also query directly each of the properties of an instance. If this is the case, just specify your server object and then the property name. For example:

$serverObject.BackupDirectory

The list of properties can be found at the end of the post.

Script to do Basic Audit of SQL Server Instance Properties

                                                                                                          
#============================================================
#Audit Script using SMO and PowerShell
#Script below enumerates all properties of a SQL Server instance,
#and exports them to a text, csv and XML file
#Author: Donabel Santos
#============================================================
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$sqlserver = "(local)"
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
#let's capture all properties into an array
$objColl = $srv | gm | where {$_.MemberType -eq "Property" } | select Name
#let's create an empty text file where we will store the server properties
$filepath = "C:serveraudit.txt"
New-Item -itemType file $filepath -force | Out-Null
#let's loop through each property
foreach($obj in $objColl) 
{ 
	#catch any exceptions
	trap [Exception] 
       { 
           $obj.Name + " :  " + "Trapped $($_.Exception.Message)" | Out-File -append -filePath $filepath
           continue
   	}
	#display property on screen; we're just curious
	" " + $obj.Name
	#we're not going to print out SystemMessages, currently there's 97526!
	if($obj.Name -ne "SystemMessages")
	{
  		$obj.Name + " :  " +  $srv.($obj.Name) `
               | Out-File -append -filePath $filepath
	}
}
#Alternative to the text file above are csv and XML files. 
#Below is how you can do it
#Notice the syntax is a little bit more cryptic - each of these is a one liner!
#Looks messy, eh?
#Export all properties to a CSV file - one liner
$srv | gm | where {$_.MemberType -eq "Property"  -and `
$_.Name -ne "SystemMessages"} `
| select Name, @{Name="Value";Expression={[string]$srv.($_.Name)}} `
| Export-Csv -path "C:serveraudit.csv" -noTypeInformation
#Export all properties to an XML file - one liner
$srv | gm | where {$_.MemberType -eq "Property"  -and `
$_.Name -ne "SystemMessages"} `
| select Name, @{Name="Value";Expression={[string]$srv.($_.Name)}} `
| Export-Clixml -path "C:serveraudit.xml" -encoding "UTF8"

List of SQL Server Instance Properties ..

that you can query from PowerShell

  ActiveDirectory
  AuditLevel
  Audits
  BackupDevices
  BackupDirectory
  BrowserServiceAccount
  BrowserStartMode
  BuildClrVersion
  BuildClrVersionString
  BuildNumber
  Collation
  CollationID
  ComparisonStyle
  ComputerNamePhysicalNetBI
  Configuration
  ConnectionContext
  Credentials
  CryptographicProviders
  Databases
  DefaultFile
  DefaultLog
  DefaultTextMode
  Edition
  Endpoints
  EngineEdition
  ErrorLogPath
  Events
  FilestreamLevel
  FilestreamShareName
  FullTextService
  Information
  InstallDataDirectory
  InstallSharedDirectory
  InstanceName
  IsCaseSensitive
  IsClustered
  IsFullTextInstalled
  IsSingleUser
  JobServer
  Language
  Languages
  LinkedServers
  LoginMode
  Logins
  Mail
  MailProfile
  MasterDBLogPath
  MasterDBPath
  MaxPrecision
  Name
  NamedPipesEnabled
  NetName
  NumberOfLogFiles
  OleDbProviderSettings
  OSVersion
  PerfMonMode
  PhysicalMemory
  Platform
  Processors
  Product
  ProductLevel
  Properties
  ProxyAccount
  ResourceGovernor
  ResourceLastUpdateDateTim
  ResourceVersion
  ResourceVersionString
  Roles
  RootDirectory
  ServerAuditSpecifications
  ServerType
  ServiceAccount
  ServiceInstanceId
  ServiceMasterKey
  ServiceName
  ServiceStartMode
  Settings
  SqlCharSet
  SqlCharSetName
  SqlDomainGroup
  SqlSortOrder
  SqlSortOrderName
  State
  Status
  SystemDataTypes
  SystemMessages
  TapeLoadWaitTime
  TcpEnabled
  Triggers
  Urn
  UserData
  UserDefinedMessages
  UserOptions
  Version
  VersionMajor
  VersionMinor
  VersionString

Rating: 10.0/10 (8 votes cast)

Rating: +2 (from 2 votes)

SQL Server PowerShell : How to Audit Your SQL Server Instance Properties Using PowerShell and SMO, 10.0 out of 10 based on 8 ratings

Be Sociable, Share!