very urgent audit looging question

Question

very urgent audit looging question

Ankit Bhurat

SSC Journeyman

Points: 89
More actions
July 22, 2008 at 12:22 pm

#123236

Hello All
I very urgently need to know; in my database that
1. Which user deleted the entire table records.
2. What sort of queried were executed by a particular user.
I know there is some kind if auit information that is being logged into the database. But I am not sure where it is being logged and how can I retrive it.
Kindly, help me is this regard. As this could save my friends job.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If there's a trigger-based audit log, that's the place to check. It's not a standard feature, you'll have to find out how it's set up in your particular database.

Otherwise, you could always check the SQL transaction log. ApexSQL has a log reader that you can download a free trial of (www.apexsql.com). Unless you're in Simple Recovery mode, or truncating the log, you should be able to find out pretty easily.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

As for what queries are run, that will have to be profiler or a server-side trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ankit Bhurat SSC Journeyman Points: 89 More actions · Answer 3

Many thanks for your quick reply.

I was just wodering if there is some DB which I can attach and do something like:

SELECT * FROM [TableName] where Database = 'ReportDB' and Userid = 'John' and TransDate = '07/22/2008'

and it gives me all the queries that 'John' executed against the 'ReportDB' Database.

Thanks

GSquared (7/22/2008)
If there's a trigger-based audit log, that's the place to check. It's not a standard feature, you'll have to find out how it's set up in your particular database.
Otherwise, you could always check the SQL transaction log. ApexSQL has a log reader that you can download a free trial of (www.apexsql.com). Unless you're in Simple Recovery mode, or truncating the log, you should be able to find out pretty easily.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Ankit Bhurat (7/22/2008)
I was just wodering if there is some DB which I can attach and do something like:
SELECT * FROM [TableName] where Database = 'ReportDB' and Userid = 'John' and TransDate = '07/22/2008'

No. Not in SQL 2005, unless someone set up custom traces and reporting.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ankit Bhurat SSC Journeyman Points: 89 More actions · Answer 5

Thanks for the reply.

I have 1 last question:

Will the ApexSQL show me the User, Data-Time and the query/SP that was executed for a particular DB?

Thanks

ANkit

GilaMonster (7/22/2008)
Ankit Bhurat (7/22/2008)
I was just wodering if there is some DB which I can attach and do something like:
SELECT * FROM [TableName] where Database = 'ReportDB' and Userid = 'John' and TransDate = '07/22/2008'
No. Not in SQL 2005, unless someone set up custom traces and reporting.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Apex SQL log reader?

It will show you transactions that occured. I don't know if the transaction has login/user info in it. It will show you the data that changed. How far back depends on the recovery mode of the DB

It will not show you select queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass