Logging select statements

Question

Post reply

Logging select statements

Daniel Godshall

SSC Veteran

Points: 246
More actions
January 31, 2005 at 2:34 pm

#102373

For various reasons I have been instructed to give more permissions to a user than I think the user really needs. With that tidbit as an unfortunate given, I am interested if there is a way to track the users activity, such as a 'select' statement run through Query Analyzer.
Thanks alot,
Dan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

You can use profiler to log the users activities.

Tom

Daniel Godshall SSC Veteran Points: 246 More actions · Answer 2

Thanks Tom,

Sorry I did not mention it but I have tried this approach already and have unfortunately experienced performances issues. I believe I have set up my filters to be as detailed as possible, but still no luck. I really appreciate any alternative answers.

Thank you,

Dan

Jason Hall Hall of Fame Points: 3906 More actions · Answer 3

Tracing the server is really the only way to accomplish this, since select statements are not captured in the transaction log.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 4

Are you running the profiler client on the server or on the server? Are you logging it to a database? Did you set it up to just look at one user? Specific database of interest? Not knowing your environment, I've set up traces on some slow machines before and never run into performance problems.

Tom

Daniel Godshall SSC Veteran Points: 246 More actions · Answer 5

Here are the details of the trace i set up:

1)Sql Profiler client is being run from/on same server containing the database

2)Log to database table

3) Only EventClass chosen is TSQL-SQL:BatchStarting

4)LoginName like 'the user'

5)ApplicationName like 'SQL Query Analyzer'

6)DatabaseName like 'my database'

Thanks

Dan

Antares686 SSC Guru Points: 125444 More actions · Answer 6

First and foremost with the Profiler client data is being transfered to the client live which means the client will be using memory and if across a network it will be using bandwidth. Don't use the client to create your trace.

Instead look at SQL BOL at sp_trace_create and the other sp_trace_xxxx procedures to generate a trace output file. These can be reviewed with profiler later. Just keep in mind that if you are storing on a local drive that contains the log files if the drive fills your server will stop. I suggest use the rollover option and move the files off the server periodically.