SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Roy Ernest / What, when and who? Auditing 101 - Part 2 / Latest Posts

RE: What, when and who? Auditing 101 - Part 2

coetzee.jj — Fri, 09 Mar 2012 04:58:09 GMT

Bummer!!!Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions.But great article nonetheless...:crying:

RE: What, when and who? Auditing 101 - Part 2

quackhandle1975 — Tue, 21 Feb 2012 07:24:47 GMT

Great article, thanks![b]qh[i][/i][/b]

RE: What, when and who? Auditing 101 - Part 2

Roy Ernest — Fri, 03 Feb 2012 06:36:32 GMT

Yes, that will be the third part of it. SQL Audit. I have completed 50% of that article. I need couple of more weeks to finish it off and then I will be submitting for publishing.

RE: What, when and who? Auditing 101 - Part 2

Nadrek — Thu, 02 Feb 2012 13:56:34 GMT

Thank you for the articles!Are you going to cover auditing who reads which data, as well as changes?

RE: What, when and who? Auditing 101 - Part 2

Roy Ernest — Tue, 17 Jan 2012 05:29:46 GMT

Thanks Jason, Thanks Wayne...

RE: What, when and who? Auditing 101 - Part 2

WayneS — Mon, 16 Jan 2012 14:27:19 GMT

Nice article Roy. Thanks!

RE: What, when and who? Auditing 101 - Part 2

SQLRNNR — Mon, 16 Jan 2012 12:50:08 GMT

Nicely done Roy.

RE: What, when and who? Auditing 101 - Part 2

trevor.pinkney — Mon, 16 Jan 2012 12:32:56 GMT

Hey Roy,You are right. The current method we don't catch SSMS changes. There also isn't a way to "Force" developers to supply a LogUserID. In some cases when we troubleshoot the database will say it was "Roy" that made the change, but really it was a System User because the developer script didn't update the LogUserID column. It gets messy when we delete rows from a table. The first thing we have to do is update the LogUserID for the rows we delete. Then we delete them. Looking forward to part 3.-Trevor

RE: What, when and who? Auditing 101 - Part 2

Roy Ernest — Mon, 16 Jan 2012 10:45:13 GMT

Hey Trevor, Half of the article is already done. That part covers the "who". I have to do some work on the article to cover writing to Event log. Once that is done, I will submit it for publication. Your present idea works when you have a controlled system like that. It just wont store the data if the update or select is done using SSMS. SQL Audit will be able to catch that.

RE: What, when and who? Auditing 101 - Part 2

trevor.pinkney — Mon, 16 Jan 2012 09:23:11 GMT

Hey Roy,Looking forward to Part #3. I really think 'Who' changed the data or 'What Process' is critical when it comes to logging. I wanted to let you know how my company handles auditing 'WHO' in the hopes you may 'speak to it' in your next article. We use a table similar to "Product". In the stored procedures that change data in this table we force developers to specify a LogUserID and a LogProcessID. The LogUserID represents the person logged into the system that pressed the 'save button' or 'delete button' on the GUI or it may be a system user. The LogProcessID is used to indicate if the change was triggered by a Web Application, A Nightly 'Product Price Update' Job sql server job, a windows service, a web service etc. PRODUCT TABLE SCHEMA----------------------------------ProductIDDescriptionPriceLogUserIDAppProcessIDDateTimeModifiedDateTimeInsertedPRODUCT TABLE SCHEMA IN LOG DATABASE - A trigger inserts into a duplicate table------------------------------------------AuditIDActionProductIDDescriptionPriceLogUserIDAppProcessIDDateTimeModifiedDateTimeInsertedAnyway - I am really curious about the 'Who' in part #3 and hope you can cover this scenario in your article.

RE: What, when and who? Auditing 101 - Part 2

Roy Ernest — Mon, 16 Jan 2012 08:06:39 GMT

I gave a recommendation that for CDC to use Snapshot isolation due to two reason.1. To make sure that there is no blocking caused when trying to get the LSN.2. To make sure that you get the right LSN.On a busy OLTP server, you are going to have high number of data changes and that means that the Max LSN will be changing at a very rapid rate. You want to make sure that the MAX LSN is the same through out the query you are using to retrieve the changes. But it all depends on how you are retrieving the changes. There fore it is just a recommendation. It is not a must. I hope I was able to answer that question.

RE: What, when and who? Auditing 101 - Part 2

Jack Corbett — Mon, 16 Jan 2012 07:49:08 GMT

Nice article. One question, you mention that it would be a good idea to have the database in Snapshot Isolation mode, but you don't really give any details as to why? I'd really like to know why I should use snapshot isolation along with CDC.

RE: What, when and who? Auditing 101 - Part 2

Roy Ernest — Mon, 16 Jan 2012 06:46:36 GMT

Who can be done by SQL Audit... I am half way through writing that article. That is the 3rd part of this series.

RE: What, when and who? Auditing 101 - Part 2

lotusnotes — Mon, 16 Jan 2012 06:09:47 GMT

CDC is fine for tracking DATA but what about WHO changed it? I can't even seem to write a join query to show the changes to the data by who?Back to audit triggers then, unless anyone can enlighten me.Thanks

What, when and who? Auditing 101 - Part 2

Roy Ernest — Sun, 15 Jan 2012 02:30:13 GMT

Comments posted to this topic are about the item [B]What, when and who? Auditing 101 - Part 2[/B]