Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Gregory Ferdinandsen,
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.
ODBC tests fine, but application creates an error
ERROR CREATING PROXY ACCOUNT....
create students accounts to log in through sql analyzer
how to connect client applications to sql server 2005
How create security for an application ?