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?
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.
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
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
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/
