SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)

SQL Server Access Restriction

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.


Total article views: 3086 | Views in the last 30 days: 1
Related Articles

Application Cannot Connect

ODBC tests fine, but application creates an error





need help to create students accounts!!!

create students accounts to log in through sql analyzer


Connecting client applications to sql server

how to connect client applications to sql server 2005


Create security for an application.

How create security for an application ?