auditing data changes, inexpensively

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » auditing data changes, inexpensively

auditing data changes, inexpensively

Rate Topic

Display Mode

Topic Options

Author

Message

Indianrock

Posted Friday, September 12, 2008 10:23 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:47 AM
Points: 564, Visits: 1,454

For now I'd like to avoid buying a tool. Need to log inserts, updates, deletes by a small group of developers who access production data outside of the GUI built for our customers. Triggers on specific fields could miss some. Tracing changes by a named group of domain users, limited to Insert/Update/Delete actions by any tool other than our GUI would do it, but I'd have to make sure it wasn't overloading the production hardware. A table on another server, loaded with such trace info, could be queried, emailed and purged on a regular basis.

Any better ideas or inexpensive ( under $400 say ) tools you've used for this?

thanks,
Randy

Post #568710

Luke L

Posted Friday, September 12, 2008 10:36 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:12 PM
Points: 2,891, Visits: 5,857

You said triggers wouldn't work, how's come? It's free and there are plenty of examples of ways to do it rather well.

Another option would be to capture a profiler trace filtering all events by user name and perhaps application name. granted there's a performance hit for these things it should get you into the right ballpark.

Sure, could someone come along and stop your profiler trace, absolutely, but if you set up your security correctly you can mitigate many of those types of issues.

Is this for compliance with a specific company, national, industry regulation? If so you may need to talk to your legal folks and see exactly what they need so that you can give it to them.

-Luke.

To help us help you read this

For better help with performance problems please read this

Post #568721

Indianrock

Posted Friday, September 12, 2008 10:47 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:47 AM
Points: 564, Visits: 1,454

I was thinking triggers were tied to specific fields in specific tables. With a large relational database, hundreds of tables, if a user makes a change to one field in one record wouldn't it be hard to set up triggers for all of that?

Yes I should get more detail because if this is a client audit requirement or SAS 70 type requirement it will have to be bulletproof.

Post #568728

GSquared

Posted Friday, September 12, 2008 10:59 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Take a look at these articles and their discussions. Lots of data and options for auditing.

http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/

- 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 #568741

Steve Jones - SSC Editor

Posted Friday, September 12, 2008 11:17 AM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

Those are good articles. Profiler will work if you can limit how the developers access (meaning trace their logins).

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #568778

Indianrock

Posted Sunday, September 14, 2008 1:10 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:47 AM
Points: 564, Visits: 1,454

Those are great articles, thanks. Since I only need to audit a handful of employees ( everyone else would be using the GUI front end or it would be an automated process loading data ) I'd rather not deal with log files or an additional database on the production hardware.

I think you can get pretty granular with profiler as far as only logging transactions by a named group of people or "anything except application A or application B". I still need to find out if this is for some legal requirement where the ability to stop the trace by a production support developer ( those guys are given sysadmin rights against my wishes ) would totally void the audit approach.

I think I've only run profiler from management studio where obviously if the workstation or server I'm remoted into is restarted the trace stops. I'll have to see how to have the trace run ( server side? ) in a way that only ( sysadmins? ) can stop it. My guess is it would satisfy the audit requirement to identify data changes if only 3 or 4 people could stop the trace.

Otherwise I'm guessing it would have to be something purchased specifically for this purpose that is virtually fullproof.

Post #569205

Steve Jones - SSC Editor

Posted Sunday, September 14, 2008 1:23 PM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

I think you'd want to set the trace to run at startup and then log to a table in a database that very, very few people (and not the DBAs) have access to.

You can always log everything from group A, and then delete stuff that has application X in it if you need to. I'd set an alert on the trace stopping, perhaps even shutting down SQL Server if you need it. Not perfect, but it will work.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #569208

« Prev Topic | Next Topic »

Permissions