x

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?
more ▼

asked Apr 21, 2011 at 01:07 AM in Default

Hum gravatar image

Hum
41 4 4 5

What version of SQL Server?
Apr 21, 2011 at 01:10 AM ThomasRushton ♦
It is 2008.
Apr 21, 2011 at 01:56 AM Hum
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Apr 21, 2011 at 01:29 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Thanks It is 2008.
Apr 21, 2011 at 01:56 AM Hum
(comments are locked)
10|1200 characters needed characters left

If you're on 2008 and above, as you say, I would investigate Extended Events, as well as the inbuilt SQL Server auditing options including [Database Auditing][3].

[3]: http://msdn.microsoft.com/en-us/library/cc280472(v=SQL.100).aspx
more ▼

answered Apr 21, 2011 at 02:25 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

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 :)
Apr 21, 2011 at 11:27 AM Hum
(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered Apr 22, 2011 at 06:41 AM

KenJ gravatar image

KenJ
20.3k 1 4 12

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 21, 2011 at 10:02 AM

Slick84 gravatar image

Slick84
1.3k 75 102 142

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.
Apr 21, 2011 at 11:32 AM Hum
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.
Apr 21, 2011 at 12:29 PM Slick84
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1846
x84
x43
x4

asked: Apr 21, 2011 at 01:07 AM

Seen: 1886 times

Last Updated: Apr 21, 2011 at 03:47 AM