SQL Server 2008 Change Data Capture, who made the change?

sql sql-server-2008 auditing cdc
MrEdmundo picture MrEdmundo · May 15, 2009 · Viewed 14k times · Source

I asked a question on SOF a week or so ago about auditing SQL data changes. The usual stuff about using triggers came up, there was also the mention of CDC in SQL Server 2008.

I've been trying it out today and so far so good, the one thing I can't see it supports is keeping a track of who actually made the change. Who executed the statement?

I'm interested to know if anyone has used CDC for auditing and how you kept track of who made the change?

Answer

LostAtC picture LostAtC · Aug 20, 2010

I altered the CDC table directly using: ALTER TABLE cdc.dbo_MyTable_CT ADD UserName nvarchar(50) NULL DEFAULT(SUSER_SNAME())

BTW you don't need the date info since it's already in the start and end LSN fields.

My only problem is that my users login via a Windows Group which allows them modify rights, but the UserName field is always my username and not theirs. I haven't found a way around this problem.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More