Database auditing

Question

Database auditing

halifaxdal

SSCoach

Points: 19741
More actions
August 5, 2008 at 2:10 pm

#191990

Hi folks,
It may be a simple question, but I just have no idea if this is available or not, either in 2000 or 2005:
Suppose I was appointed to DBA for a database, it is not well documented and the organization has just been re-orged so everything seems messy. Now my boss want to know who used the server(and database) for the last month, or last year, is it possible?
If I can't trace back, can I start monitoring now? I want an automation to tell send me a email everyday to tell me who is my client.
Thanks lots.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

I think you can use sp_who2 or values in the master..sysprocesses table You can view the host pc, program name, db name accessed by the hostname etc.

"-=Still Learning=-"

Lester Policarpio

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Hi,

Thank you so much for the hint, do you know if there is a way to trigger the sp_who command when somebody access a specific server/database? Although sp_who lists users info, it seems work for current user only. But I can't always run this command, right? it will be nice if I can create kind of trigger to run this command, whenever a user accesses my server, or database, or specific table.

Any more idea?

Thanks lots.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 3

You can use the data in master..sysprocesses, make a stored procedure schedule it to run lets say every 3-5 min then sends you a report or alerts you via email etc. if it captures users accessing your server.

"-=Still Learning=-"

Lester Policarpio

halifaxdal SSCoach Points: 19741 More actions · Answer 4

I checked the sysprocesses (SQL2000), it gives me only four days records, is there any option I should set to make the log longer?

Thanks.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 5

halifaxdal (8/6/2008)
I checked the sysprocesses (SQL2000), it gives me only four days records, is there any option I should set to make the log longer?
Thanks.

but you can always make another table and save the gathered file there for auditing....

"-=Still Learning=-"

Lester Policarpio

halifaxdal SSCoach Points: 19741 More actions · Answer 6

Sorry I didn't quite get you.

The problem seems to be there is no much information has been retained in table sysprocesses, I am just wondering is there any option I need to turn on so that I can see more history in that table?

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 7

What else do you need to monitor???

"-=Still Learning=-"

Lester Policarpio

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

In SQL 2000, I don't think there are any ways to determine who had been on the server in the past unless C2 auditing is on. In SQL 2005 there is a trace running by default that may have this information. On either 2000 or 2005 you can setup a server-side trace that audits logins and you can archive the the files or load them into a database to keep as long as you need.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question