Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

IV.3.3. Logon Triggers: Monitoring and Controlling Login Events

Logon triggers are used to audit and control server sessions. When configured, a user-defined stored procedure fires in response to a login event. Some examples of how a logon trigger is used include:

• Track or audit all login activity: You can use a logon trigger to capture logon information (such as who logged in and when they logged in) into an audit table.

• Restrict logins: Logon triggers can be used to restrict logins to the SQL Server during certain times. For example, you might want to restrict logins during maintenance periods.

• Restrict logins for specific users: You can use logon triggers to restrict a user from having more than a specific number of active sessions, or restrict a user's total monthly logon time.

The timing of when logon triggers fires gives you insight into how they can be used.

1. User authenticates: A user initiates a session with SQL Server. Credentials are passed and the user is authenticated.

2. Logon trigger fires: After the user authenticates (but before the session begins), the logon trigger fires. The logon trigger can capture information about the user. This information can be logged, or used to rollback the login and prevent the session.

3. Session begins (or is prevented by logon trigger): If the login doesn't rollback, the user's session begins.