Requires SQL 2005 SP2 or higher
1) Create a new database called SQL_Audit.
2) Create the two tables in the SQL_Audit database
3) Change the <<with Execute as 'Domain\SQL' for logon>> on line 89 to an account on your server with sa rights
4) Create the logon trigger
You can easily alter the conditions in the logon trigger to suit your needs. All conditions are evaluated out of the BlackList table, any combinations of events can be set to disallow a connection to the sql server.
Examples of useage:
Condition #1: Prevent a Application Service Account from logging on from certain host names (or change the logic so that an Application Service Account can only logon from certain host names or IP addresses)
Condition #4: Block all connection from a given host
Condition #6: Block all connection using an Application Service Account if the connection is coming from a particular application (i.e. SSMS)
Any number of new conditions may be created in the trigger.
For most efficient execution, first evaluate a condition that is true and hardcode the parameters (such as is the connection coming from the application server farm, using a service account and coming from .net/IIS, etc). If the condition is true, exit the trigger. This will prevent unnecessary table lookups.
