Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 2924 | Views in the last 30 days: 3
 
Related Articles
FORUM

Application Cannot Connect

ODBC tests fine, but application creates an error

FORUM

ERROR CREATING PROXY ACCOUNT....

ERROR CREATING PROXY ACCOUNT....

FORUM

need help to create students accounts!!!

create students accounts to log in through sql analyzer

FORUM

Connecting client applications to sql server

how to connect client applications to sql server 2005

FORUM

Create security for an application.

How create security for an application ?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones