August 16, 2002 at 1:06 pm
Management is requiring that our personnel run a Crystal Report query every hour against the SQL database. They (management) want me to tell them if anyone isn't doing this. The users aren't required to run the query at any specific time, just once an hour. Each user has their own login and password to the database that they use to connect Crystal Reports to my database. How can I get a record/file/whatever of when someone logged in and what they used? For example: I can go into Enterprise Manager and using Management > Current Activity > Process Info, I can find the user, application, login time, and last batch time. I would like to pull that information whenever someone logs into the database and uses Crystal Reports.
-SQLBill
August 16, 2002 at 2:21 pm
Use Profiler. You can track who, when, what they did, and what application they used. You can also filter to ensure you only get the database in question.
Write the information to a trace file initially (fastest), then bring up the trace file again in Profiler and save to a trace table. You can then do your standard analysis except against a SQL table.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 19, 2002 at 12:35 pm
The sound you may have heard this morning is me slapping my forehead. Profiler is giving me just what I wanted. Now I'm going to check out the site you suggested. I need to find out a way to let management see the trace. They currently don't have Profiler on their computers, so I'm looking to see if it can be saved as a .txt or .doc file and viewed that way. Also, I need to find out if I can keep it running while I'm not logged in; currently the trace stops when I log out.
-SQLBill
August 19, 2002 at 1:04 pm
Best way to let them see the data is to put it in a trace table as a staging point and then put it in a usable form in another table. Then write a web front end.
As for automating traces, take a look at the sp_trace_* system stored procedures. You can create traces and schedule their execution through SQL Agent.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply