SQL Server Audit Script - Part 1
A SQL Server Audit is often a time consuming manual process. This article describes a script I have written to make it less of a chore!Whenever I consult for a new client the first thing I want to do is to understand their environment. What operating system(s) do they have? What SQL Server editions? What configuration settings have been used? What are the names of the databases? How big are they? Do they follow best practice?
In a large environment this can be very time consuming. Some clients already have this information documented, or have purchased tools for managing the environment. Others do not.
To solve the problem I have written a simple script to do the job for me. I have also written scripts for daily monitoring and batch updates, but these are for a future article.
In this article I demonstrate what my script does and provide some screenshots. In the next article I give you the code and explain how to use it.
The script is packaged as an HTA file called SQL Audit.hta. It reads the servers and instances to be audited from an xml file (SQL Servers.xml). Here's what it looks like when first started: SQLServerAudit1.gif.
Initially, all servers are displayed but these can be filtered by selecting a group in the dropdown. Change the user name, if necessary, enter your password, select one or more servers/instances and hit Run. Clicking the word "Configuration" will select or deselect all servers or instances. An example of what you see just before clicking Run is: SQLServerAudit2.gif.
The output is as follows: SQLServerAudit3.gif. You can see that there is a lot of useful information about the servers, instances and databases. Any deviation from best practice (such as AutoShrink = On) is displayed in red.
I realise that all of this is not much use to you without the code, so read SQL Server Audit Script Part 2 for all the details.
