February 11, 2010 at 12:44 pm
Hi folks,
I have some external users who have access to my database/server, for an auditing purpose, I want to know what resource the user is accessing?
The user logs in using SQL authentication, I know I can use Activity Monitor, not much helpful, I opened Profiler, too much options, I don't know what should I turn on, now I turned on Audit Login, just keep getting Event Class: Audit Login
The content is as following:
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Microsoft SQL ServerEPM2016612010-02-11 14:36:58.353
EPM is the account I created for the user.
I don't see more useful details here.
Can anyone help? Thanks.
February 11, 2010 at 2:15 pm
Audit Login and filter by login name in column filter.
February 11, 2010 at 2:30 pm
To know what resource the user is accessing you'll need the rpc: batch completed (stored procedure) and SQL Batch Completed (ad-hoc SQL).
There are filters for loginname in your trace filters. Check the checkbox for show all columns too.
You'll get a lot more information by picking some of the extra columns that don't come by default.
A Profiler trace can be very resource intensive and reduce system performance noticably.
If you filter on the one username, you should be OK if you don't intend to run the trace for an extended periiod.
If you do want to audit over a broad range of time, you'll need to run a server-side trace and save to a file. Doing that is not a huge resource penalty like running the Profiler is.
Craig Outcalt
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply