Database Audit

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Database Audit

Database Audit

Rate Topic

Display Mode

Topic Options

Author

Message

muthyala_51

Posted Wednesday, October 24, 2012 2:24 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 232, Visits: 1,076

Hi,
I just sent up an audit on a database to collect all the select queries on a table. Everything is working fine if we use just select statmenet with where condition. But if we use a variable it's not capturing the variable value. It just grabs the variable name. How to overcome this? I am using 2008 sp3 enterprise edition.

Post #1376669

bjcharles

Posted Thursday, October 25, 2012 7:28 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:04 AM
Points: 13, Visits: 102

Try 'Change Data Capture', it can capture DML statements

Post #1376980

muthyala_51

Posted Thursday, October 25, 2012 12:03 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 232, Visits: 1,076

Is there any other way we can do it other than CDC?

Post #1377187

GSquared

Posted Thursday, October 25, 2012 12:28 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

Server-side traces and extended events are how I do that kind of thing. You can capture any DML events you want to, and filter them pretty much however you like, using either of those.

I recommend extended events, because Microsoft says they'll sunset traces in a future version of SQL Server and to use extended events for new development.

Data on the subject here: http://msdn.microsoft.com/en-us/library/dd822788(v=SQL.100).aspx

- 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

Post #1377196

Jeff Moden

Posted Thursday, October 25, 2012 9:55 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

As Gus suggested, a server side trace with a LIKE filter looking for the table name will work quite nicely. So would "extended events".

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1377378

« Prev Topic | Next Topic »

Permissions