Capturing Logon Trigger Event Data

To capture XML data about LOGON events for use inside logon triggers, use the EVENTDATA function. For more information, see Designing and Implementing Structured Storage (Database Engine). The LOGON event returns the following event data schema:

Best Microsoft MCTS Training – Microsoft MCITP Training at Certkingdom.com

 

<EVENT_INSTANCE>

<EventType>event_type</EventType>

<PostTime>post_time</PostTime>

<SPID>spid</SPID>

<ServerName>server_name</ServerName>

<LoginName>login_name</LoginName>

<LoginType>login_type</LoginType>

<SID>sid</SID>

<ClientHost>client_host</ClientHost>

<IsPooled>is_pooled</IsPooled>

</EVENT_INSTANCE>

<EventType>

Contains LOGON.
<PostTime>

Contains the time when a session is requested to be established.
<SID>

Contains the base 64-encoded binary stream of the security identification number (SID) for the specified login name.
<ClientHost>

Contains the host name of the client from where the connection is made. The value is ‘&lt;local_machine&gt;’ if the client and server name are the same. Otherwise, the value is the IP address of the client.
<IsPooled>

Is 1 if the connection is reused by using connection pooling. MCTS Online Training
MCITP Online Training Otherwise, the value is 0.

Creating, Modifying, and Dropping Logon Triggers

Logon triggers can be created from any database, but are registered at the server level and reside in the master database.
To create a logon trigger

*

CREATE TRIGGER (Transact-SQL)

To modify a logon trigger

*

ALTER TRIGGER (Transact-SQL)

To drop a logon trigger

*

DROP TRIGGER (Transact-SQL)