October 12, 2007 at 2:08 pm
In SQL Server 2008. I have tried to enable table change data capture auditing. This create a mirror table for each table I want to audit. The mirror table have 5 columns that the original table does not. One of the new mirror column is __$start_lsn and I can join this to cdc.lsn_time_mapping to get the transaction id. My question is does anyone know to get the user login for a particular transaction id. or LSN?
LSN = 0x0000001B000000340003
transactionid = 0x000000000289
Can I somehow use this information to join some view or system table to get the user id that own the transaction?
Thanks,
Louis.
October 12, 2007 at 5:11 pm
Only by using the undocumented log scanning function fn_dblog, and only if the log record you're interested in hasn't been truncated from the log. It's complicated but do-able.
If you want to do auditing, there's an all-actions audited feature coming in 2008. I suggest you use it instead.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 16, 2007 at 1:21 pm
Paul,
How do I find out more about this feature? I don't seem to be able to find this in Book online on the July 2008 version. Googling this only talk about the feature a little bit. Is there a link to this? Or is this a new coming feature into newer release of 2008? So does it do everything change data capture do plus refering the user to each audit record?
Thanks,
Louis.
October 16, 2007 at 2:33 pm
It'll be in one of the future CTPs of SS2008. See http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_oltp.mspx for more info.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply