tracking\logging database access

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  November 14, 2008 at 5:49 am

  #202816

  Hi people.

  I need sql to log activity happening in our database, particularly when requests are made to it.

  e.g.

  1. Requesting system's name

  2. Requesting username

  3. Data Requested

  4. Action Performed

  5. Date and Time of request

  any information or a point in the right direction will be much appreciated.

  Thanks

• ajoy_gochhayat

  SSC Enthusiast

  Points: 167

  More actions

  November 14, 2008 at 6:42 am

  #898531

  Please run the below statement and you will get an idea to achive your goal.

  select * from master.sys.sysprocesses where spid=@@spid

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  November 17, 2008 at 12:22 am

  #899187

  thanks... had anyone else worked on something similar who can offer some more advice?

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  November 17, 2008 at 12:41 am

  #899190

  try sp_who2. it will give you the detailed information about who all are logged into the server and the tasks being performed by them.

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  November 17, 2008 at 12:47 am

  #899191

  Read up on DDL triggers. They can help.

  "Keep Trying"

• Kishore.P

  SSCrazy Eights

  Points: 8147

  More actions

  November 17, 2008 at 1:07 am

  #899197

  SQL 2005 and above version use DDL Triggers, may help to you.

  SQL 2000 and below version, use sp_who2 or sp_who3

  for sp_who3 Links:

  http://vyaskn.tripod.com/sp_who3.htm

  http://www.sqlservercentral.com/scripts/Miscellaneous/31109/

  http://www.eggheadcafe.com/software/aspnet/28578981/spwho3.aspx