473,237 Members | 1,392 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Tracking user activities

Sybase and DB2 both have the capability of tracking user activities at
a number of levels: invalid access attempts to databases, table, etc.;
creation/deletion/modification of database objects/users/groups,
grants/revokes.

For MS SQLServer, the only setting that I've seen in the documentation
is access attempts (none, fail only, etc.)

The monitor program has the capability of tracking the events that I
want to be monitored, but it seems as though these settings persist
only while the monitor program is running.

I'd like these settings to persist permanently and the event records to
be sent to the system log.

I can't seem to find the right term to get this information out of the
MS Books On LIne.

Help!

Oct 14 '05 #1
5 5326
byrocat (bd******@sympatico.ca) writes:
Sybase and DB2 both have the capability of tracking user activities at
a number of levels: invalid access attempts to databases, table, etc.;
creation/deletion/modification of database objects/users/groups,
grants/revokes.

For MS SQLServer, the only setting that I've seen in the documentation
is access attempts (none, fail only, etc.)

The monitor program has the capability of tracking the events that I
want to be monitored, but it seems as though these settings persist
only while the monitor program is running.

I'd like these settings to persist permanently and the event records to
be sent to the system log.

I can't seem to find the right term to get this information out of the
MS Books On LIne.


In SQL 2000, this is not very simple to do. In SQL 2005, which is just
around the corner you can create triggers on database and server to
capture this kind of stuff.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 14 '05 #2

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
byrocat (bd******@sympatico.ca) writes:
Sybase and DB2 both have the capability of tracking user activities at
a number of levels: invalid access attempts to databases, table, etc.;
creation/deletion/modification of database objects/users/groups,
grants/revokes.

For MS SQLServer, the only setting that I've seen in the documentation
is access attempts (none, fail only, etc.)

The monitor program has the capability of tracking the events that I
want to be monitored, but it seems as though these settings persist
only while the monitor program is running.

I'd like these settings to persist permanently and the event records to
be sent to the system log.

I can't seem to find the right term to get this information out of the
MS Books On LIne.


In SQL 2000, this is not very simple to do. In SQL 2005, which is just
around the corner you can create triggers on database and server to
capture this kind of stuff.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


I don't know how simple it is, but if the event is captured by SQL Profiler
you can use Profiler to define a trace and then under the file menu item
select Script Trace. This will generate a script that uses sp_trace_create,
sp_trace_setevent, sp_trace_setfilter, and sp_trace_setstatus to create a
server side trace. You can then use fn_trace_gettable to retreive data from
the trace file as a table.

Mike Reigler
Oct 14 '05 #3
MIke, thanks for the reply. Two more quick questions:

So, if we go through the exercise of defining the events that we wish
to trace and generate the script file and then run the script file,
this means that the settings are permanent (i.e., rebooting the server
will not terminate the settings)?

Can I also assume that the script can be used to set up can be used on
other servers without changes?

Thanks in advance!

Oct 14 '05 #4

"byrocat" <bd******@sympatico.ca> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
MIke, thanks for the reply. Two more quick questions:

So, if we go through the exercise of defining the events that we wish
to trace and generate the script file and then run the script file,
this means that the settings are permanent (i.e., rebooting the server
will not terminate the settings)?

Can I also assume that the script can be used to set up can be used on
other servers without changes?

Thanks in advance!


The server side trace is not really permanent it just runs from when you
start it until it is stopped. I think rebooting the server would stop the
trace. You would have to use job scheduling to run the script on startup.

Also when you create the trace you specify how large the trace file can be
(the default is 5 MB). If you want to permanently capture the data you
would need to have a script scheduled to run periodically before the file
gets to the max size and copy the data from the trace file into a permanent
table and probably start a new trace.

Not exactly simple, but as long as your trace doesn't filter on specific
users etc. it should be portable to other servers.

All that being said, I've never run a trace permanently just used them to
troubleshoot a specific problem.
Oct 14 '05 #5
byrocat (bd******@sympatico.ca) writes:
So, if we go through the exercise of defining the events that we wish
to trace and generate the script file and then run the script file,
this means that the settings are permanent (i.e., rebooting the server
will not terminate the settings)?
First of all, since you probably want to look at the data, and you
can do that while the trace is running, you would need a job that
stops the trace, moved the file away and then restarts the trace.

You can specify a max size, and a rollover, so that the engine performs
this trick when the file has reached a certain size. But if it takes
a while for the file to fill up, that's not good.

And, no, the trace does not survive a server reboot. Put the script
in a stored procedure, and mark this procedure as a startup procedure.
Can I also assume that the script can be used to set up can be used on
other servers without changes?


Yes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 14 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: hawkon | last post by:
Hi all, I have an important question to ask about how to trap events when the user close the browser window. I'm a ASP programmer and I have s MSSQL database with a user table where I'm able to...
1
by: DDK | last post by:
Does anyone know if there are any books that talk about building a website tracking program for asp.net and C#? Or even any good resources on the web that talk about this. Thanks for any info,...
3
by: =B= | last post by:
Hi all, I was wondering if anyone has had any luck with trapping the <BODY> onUnload() event in ASP.NET? The thing is, I'm writing code for an Intranet site. The code makes a call to a...
3
by: Patrick McGuire | last post by:
I am instantiating several custom objects from a subset of data contained in an XML file. I then might edit these objects and save the changes back to the XML file. However, I want to compare the...
1
by: kasterborus | last post by:
Hi, My client is using the UBB Threads forum and I was recently asked to look into adding the ability to track the activity of each of the users to see who is more active than others. Users are...
1
by: mbsevans | last post by:
Again, just a new user here but have been charged with creating a database that captures day to day activities regarding a very large construction job. Parameters such as, plan inspections, safety...
2
by: C# programmer | last post by:
Hi All, I'm working on a project which requires tracking of recent document downloads. There is a feature in which user can download the docs without logining into the website for some of the...
4
by: diego | last post by:
Hi everyone, What is an effective way of storing user activites while using my program? Like when a user clicks on a button, i would like ro save the following in a log file, "Clicked Edit...
0
by: LiveTecs | last post by:
http://www.livetecs.com TimeLive Web Collaboration Suite is an integrated suite that allows you to manage project life cycle including tasks, issues, bugs, timesheet, expense, attendance. ...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.