question

Hum avatar image
Hum asked

Knowing Who Manipulated a Table and When

I have a database with many users having access to it with permission to insert into, delete from and update its tables. Sometimes I find rows with strange data or important ones which where deleted without an request from the database admin. I want a table to record the User Name and the time of change every time a table is manipulated. I want the simplest way to do that. I have added a column to every table recording the User Name and time of change. However, this way lacks the ability to track updates and deletes (as far as I know). I have also read about SQL Server Profiler, procedures, triggers and other sophisticated manners which did not satisfy me since I believe there are simpler ways. Does anyone have an idea?
sql-server-2008tableauditddl-changes
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What version of SQL Server?
2 Likes 2 ·
It is 2008.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
The methods you can use and the best option to put in place will depend on your SQL Server version, as the comment already points out. You may be able to use Database Triggers to catch DDL events and either prevent or audit the changes or you could use features built in to more recent versions that do it more elegantly. [Edit] By complete chance this blog has been posted today - http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/use-ddl-triggers-to-track it may be of use to you.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks It is 2008.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
If you're on 2008 and above, as you say, I would investigate [Extended Events][1], as well as the inbuilt [SQL Server auditing][2] options including [Database Auditing][3]. [1]: http://msdn.microsoft.com/en-us/library/bb630354(v=SQL.100).aspx [2]: http://msdn.microsoft.com/en-us/library/cc280386(v=SQL.100).aspx [3]: http://msdn.microsoft.com/en-us/library/cc280472(v=SQL.100).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have successfully made an audit for a specific table in the database which tracked a lot of things about the change, including: User Name, time and even the statement which caused the change! Thanks very much Thomas :)
0 Likes 0 ·
Slick84 avatar image
Slick84 answered
I'd add a default constraint in an additional column with the SUSER_SNAME builtin function. For more info check [this][1] link out. [1]: http://msdn.microsoft.com/en-us/library/ms174427.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, but I have used a similar function, system_user, previously within a trigger which fires each time a user changes a table using insert, update or delete.
0 Likes 0 ·
DML Triggers are your best freind in this situation. You could try using the OUTPUT clause and insert the values from there into your "logging/auditing" tables.
0 Likes 0 ·
KenJ avatar image
KenJ answered
I think @ThomasRushton nailed it with the Extended Events and Auditing answer, but the suggestion in the original question that Profiler is one of the sophisticated methods to be spurned because of its complexity doesn't quite set right with me. Profiler is not only a decent solution for this, it is arguably, in the form of a server-side trace, the simplest to implement, manage and report on, and I could easily recommend that approach (and sometimes do) for all but the busiest of servers. Using Profiler to set up a server-side trace is [incredibly easy][1], and it is trivial to load the trace log into a table for further analysis, if you didn't store it in a table to begin with (file is typically recommended for tracing performance related events, and it would keep your overly-privileged users away from the audit table in this case). Whichever logging method you implement, be sure to take a little time to become familiar with the Profiler/server-side trace approach to capturing these events. It will pay dividends for the rest of your career. [1]: http://sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.