Page 174 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes who have logged in or out of the server. This information can then be used for legal
purposes to document activity and for technical purposes to track security policy violations.
To set up a SQL Profiler trace that tracks users who have logged in or out of the server, do the following:
1. Create a trace, selecting Audit Login Event.

2. To return the appropriate information, specify the following data columns:
i. EventClass (selected by default)
ii. EventSubClass
iii. LoginSID
iv. LoginName
v. Monitor Transact-SQL activity per user.
You can create a trace that captures events relating to the Sessions event class, ExistingConnection,
and TSQL event classes. Include all data columns in the trace, do not specify any event criteria,
and group the captured events by DBUserName.

Task Create a SQL Profiler trace that tracks users who have logged in or out of the server.

12.5.3 Monitoring with SQL Profiler Event Categories

In SQL Profiler, use event categories to monitor events in Microsoft® SQL Server™. Event categories
contain event classes that have been grouped together within the SQL Profiler user interface.
The following table describes the SQL Profiler event categories and their associated event classes.

Event category Description
Cursors Collection of event classes produced by cursor operations.
Database Collection of event classes produced when data or log files grow or shrink
automatically.
Errors and Warnings Collection of event classes produced when a SQL Server error or warning
occurs (for example, an error during the compilation of a stored procedure or
an exception in SQL Server).
Locks Collection of event classes produced when a lock is acquired, cancelled,
released, etc.
Objects Collection of event classes produced when database objects are created,
opened, closed, dropped, or deleted.
Performance Collection of event classes produced when SQL data manipulation (DML)
operators execute.
Scans Collection tables and indexes are scanned.
Security Audit Collection of event classes used to audit server activity.
Sessions Collection of event classes produced by clients connecting to and disconnecting
from an instance of SQL Server.
Stored Procedures Collection of event classes produced by the execution of stored procedures.
Transactions Collection of event classes produced by the execution of Microsoft Distributed
Transaction Coordinator (MS DTC) transactions or by writing to the
transaction log.
TSQL Collection of event classes produced by the execution of Transact-SQL
statements passed to an instance of SQL Server from the client.
User Configurable Collection of user-configurable event classes.

168 LOVELY PROFESSIONAL UNIVERSITY