SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by GSquared / Audit Trails and Logging Part II / Latest Posts

RE: Audit Trails and Logging Part II

Abrar Ahmad_ — Fri, 23 Jul 2010 04:33:31 GMT

[font="Verdana"]I am curious, that no one has yet commented over CDC feature built-in in SQL Server 2008?It is not such out off the domain of Auditing?Please?[/font]

RE: Audit Trails and Logging Part II

_UB — Thu, 13 Aug 2009 11:53:41 GMT

Gsquared,Thanks for the informative article.One question though. My question is to know if there is any advantage of one method over the other. Or is it just preference (...I say toMato and you say TomaTo case)In part II, active logging with triggers, on INSERT the code stores the INSERTED record. On DELETE, it stores the DELETED record. But on UPDATE it stores the new record from the INSERTED table rather than the row that is being deleted.I've always stored the row that is being deleted (i.e. from DELETED table except for INSERT). I see both methods to be of same value. but wanted to know what you think of it.thanks,_UB

RE: Audit Trails and Logging Part II

GSquared — Mon, 04 Aug 2008 12:06:02 GMT

Some expansions on this subject at:[url]http://www.sqlservercentral.com/Forums/Topic536927-145-1.aspx[/url]

RE: Audit Trails and Logging Part II

Jeff Moden — Mon, 21 Jul 2008 20:10:16 GMT

Somehow, I missed this article before. Awesome job, Gus. Huge amount of good info and some great technique comparisons!

RE: Audit Trails and Logging Part II

Eric Stimpson — Wed, 11 Jun 2008 10:32:50 GMT

If you plan to be able to "recreate" a result set using audit tables and the underlying query, keep in mind that a join to a non-audited table could make it impossible to recreate the desired result set, even to just determine which rows were returned. You're stuck with either auditing all tables that might be joined to the table your concerned with or relying on backups of transaction logs to be able to restore the database to a particular point in time and rerun the query when a non-audited table is used.

RE: Audit Trails and Logging Part II

SuperDBA-207096 — Wed, 11 Jun 2008 04:28:36 GMT

Nice work! I've implemented similar processes and found that they work well - one thing you need to beware of with this approach is if you make a change to a base table, you need to be sure to update the audit table and trigger.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 14:53:31 GMT

[quote][b]Thomas Keller (6/10/2008)[/b][hr]In my case, it was necessary not only to show that a certain user ran a certain query at a certain time (as the trace would do), it was also necessary to show which records resulted, and the state of certain important columns at the time, hence the need for active logging from the select procedure. Also management wanted real-time info, and did not want to archive old info. Admittedly this was not a huge database, but effective normalization keeps the history table manageable.[/quote]I have had databases where we needed to be able to track data changes and be able to present a view of the data as it existed at any point in time. This is best accomplished via triggers and archive/history tables. When combined with a trace that captures who ran what query and when, you could "recreate" what a given user saw at a given point in time without having to store the actual result set returned.

RE: Audit Trails and Logging Part II

Thomas Keller — Tue, 10 Jun 2008 14:43:27 GMT

In my case, it was necessary not only to show that a certain user ran a certain query at a certain time (as the trace would do), it was also necessary to show which records resulted, and the state of certain important columns at the time, hence the need for active logging from the select procedure. Also management wanted real-time info, and did not want to archive old info. Admittedly this was not a huge database, but effective normalization keeps the history table manageable.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 14:41:12 GMT

Eric, that's a good point about 2000. Traces in SQL 2005 have been significantly improved in several ways including making them generally less expensive. I just assumed that we were talking about 2005 and I personally haven't worked with a 2000 instance for almost two years so I just don't think about it much anymore...my mistake.

RE: Audit Trails and Logging Part II

Eric Stimpson — Tue, 10 Jun 2008 14:29:51 GMT

[quote]I was going to reply and correct that statement, but you beat me to it. No, the trace does NOT need to be stopped to examine the active trace file with the fn_trace_gettable function. And yes, the results of the query can be inserted into a table, just like any other table valued function.[/quote]In SQL 2000 when using fn_trace_gettable, if you do not stop the trace you cannot read the complete trace. In fact, there will be a sizeable number of records that have not yet been written to disk. Only stopping the trace forces all records to be written to the trace file and allows you to read the entire contents into a table. SQL 2005 does not have this issue. You can read the entire contents of the trace file even while it is running.So if you're in a SQL 2000, all you need to do is start a new trace before you close the previous, and you will not loose any transactions. Furthermore, your now closed trace can include the call to sp_trace_setstatus to show that a new trace was started. In a scenario where you need to trace continuously, you can create a job that runs once per day and uses the date in the trace filename. Even in SQL 2005, I would still use this method as it allows the trace file from previous days to be moved to another server and compressed, archived, etc.

RE: Audit Trails and Logging Part II

GSquared — Tue, 10 Jun 2008 13:50:12 GMT

DC and others who brought up server traces as an option: Thanks. I wasn't aware of that option. Sounds like something to definitely investigate a bit.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 13:26:57 GMT

[quote][b]Jack Corbett (6/10/2008)[/b][hr][quote][b]Eric Stimpson (6/10/2008)[/b][hr]You can examine the contents of the trace after you've stopped it by using this select:[/quote]Therein lies the rub, I think. A trace used for auditing would need to be running as long as the server is running or you would have to deny access to the database while you examine the log.Since I am not really familiar with server-side traces, can you output the results to a table like you can with the Profiler GUI? I would assume you can, but then you now have the performance hit of the inserts as G mentioned in one of his posts.[/quote]I was going to reply and correct that statement, but you beat me to it. No, the trace does NOT need to be stopped to examine the active trace file with the fn_trace_gettable function. And yes, the results of the query can be inserted into a table, just like any other table valued function.If the overhead of inserting the trace into a table is that much of a concern, you have options. You can write the trace file to a share and have a different instance grab and import it for example.Also for those of you who are not familiar/comfortable with setting up custom traces, you can actually use Profiler to set up a trace and then export the definition as a .sql file. This is a pretty good way of starting to crack the somewhat arcane syntax of the trace events etc...

RE: Audit Trails and Logging Part II

Jack Corbett — Tue, 10 Jun 2008 13:14:27 GMT

[quote][b]Eric Stimpson (6/10/2008)[/b][hr]You can examine the contents of the trace after you've stopped it by using this select:[/quote]Therein lies the rub, I think. A trace used for auditing would need to be running as long as the server is running or you would have to deny access to the database while you examine the log.Since I am not really familiar with server-side traces, can you output the results to a table like you can with the Profiler GUI? I would assume you can, but then you now have the performance hit of the inserts as G mentioned in one of his posts.

RE: Audit Trails and Logging Part II

Eric Stimpson — Tue, 10 Jun 2008 12:51:33 GMT

SQL tracing in general, and server-side tracing in particular (simply because of its much lower overhead) is one of my favorite tools for a wide variety of tasks ranging from auditing, performance tuning, lock and deadlock investigations to looking under the hood at how a particular application is accessing a system, or even how some of the SQL tools perform their tasks. Creating SQL traces using the sp_trace_create proc can be a little tricky for a first timer, but creating your own scripts or procedures gives you much more control over how a trace works then using the profiler, so I highly recommend you give it a shot. Here's an example script that I frequently use as a starting point:[code]declare @ProcReturn int, @TraceID intexec @ProcReturn = sp_trace_create @TraceID = @TraceID out, @Options = 2, @TraceFile = N'e:\TraceData\LockingIssues', @MaxFileSize = nullselect @ProcReturn, @TraceIDdeclare @Column int, @On bitset @Column = 1set @On = 1while @Column <= 44 begin exec sp_trace_setevent @TraceID = @TraceID, @EventID = 10, @ColumnID = @Column, @On = @On exec sp_trace_setevent @TraceID = @TraceID, @EventID = 12, @ColumnID = @Column, @On = @On exec sp_trace_setevent @TraceID = @TraceID, @EventID = 41, @ColumnID = @Column, @On = @On exec sp_trace_setevent @TraceID = @TraceID, @EventID = 43, @ColumnID = @Column, @On = @On exec sp_trace_setevent @TraceID = @TraceID, @EventID = 50, @ColumnID = @Column, @On = @On exec sp_trace_setevent @TraceID = @TraceID, @EventID = 59, @ColumnID = @Column, @On = @On set @Column = @Column + 1endexec @ProcReturn = sp_trace_setstatus @TraceID = @TraceID, @Status = 1select @ProcReturn, @TraceID[/code]This example captures all of the SQL 2000 columns for these events:RPC: Completed, SQL: BatchCompleted, SQL: StmtCompleted, SP: Completed, SQL Transaction, and Lock: Deadlock Chain.You can examine the contents of the trace after you've stopped it by using this select:[code]select *from ::fn_trace_gettable('e:\TraceData\LockingIssues.trc', default)where EndTime between '2007-11-07 07:56:00.000' and '2007-11-07 08:05:00.000'[/code]Regards,Eric

RE: Audit Trails and Logging Part II

Thomas Keller — Tue, 10 Jun 2008 11:46:58 GMT

I have had to satisfy a similar select-logging requirement, in an environment where management was worried about field sales reps selecting the database and taking it with them if they left the company. Of course we had a stored procedure to do the select, which would stop at 500 rows, but in addition I used the same mechanism as my trigger-based transaction logging, which selects the INSERTED and DELETED into a temp table, then loops through the columns thereof. Since the select sproc was using a temp table to gather the results before returning them, I just passed that temp table to the same logging sproc.

RE: Audit Trails and Logging Part II

Tore Bostrup-382308 — Tue, 10 Jun 2008 10:49:02 GMT

Lee, I presume you're working with a healtcare system or somewhere outside the US.There may be some healthcare specific products out there.Logically, capturing the "pipe" to (and from) the database on the server (similar to an ODBC trace) would probably be the "safest". However, the ODBC trace itself is client-side (and is configured on the client), so it is not "safe" for audit purposes. Several years ago, I did hear of applications that captured the IP traffic for this purpose. It would probably have to be captured on the SQL Server.If the requirement is application specific and the application is to be developed, the easiest way would probably be to architect your application with a layer that allows you to perform additional actions like capturing the results. However, the overriding issue may be how to ensure that all paths to the data is covered (other apps, ad hoc queries).Consider using security settings limited to a service account known only to your company's security administration group, and require all access to the database to go through a separate service running under this account. That service could then perform any logging required. You would have to notify the service who the actual user is, so that this can be logged.If this is not an option, it *may* be possible to implement a proxy database ("log and forward") or alternatively, maybe a SQL trace file approach could be combined with a trace scanner that would repeat the SELECT queries to capture the results (against a replicated DB?). This would have some challenges of its own, I suspect.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 10:33:19 GMT

[quote][b]GSquared (6/10/2008)[/b][hr]DCPeterson, on the subject of a "server side trace". I'm not clear on what you mean by that. Are you talking about something outside the database, or some feature of SQL 2005/2000 that I'm unaware of? (I've read that SQL 2008 will offer more built-in auditing features, but haven't had a chance to play with those yet.) Or do you just mean running Profiler constantly?Just running Profiler constantly will give you a log, but the files will quickly fill up a lot of disk space, and can't be queried the way log tables can (at least, not easily). If you have Profiler inserting into tables, the performance hit is just about the same as using triggers, as is the space used.Could be done. Like any of the other methods, it has advantages and disadvantages. I can't think of an easy way to query such data to readily get the complete history of a particular row in a particular table, to run undo commands, or to track workflow data for management reports. On the other hand, you could get performance data right in your audit log, which could be useful for other things.[/quote]First off, I think it helps to get terminology correct. A trace refers to the built in capability of SQL Server to capture defined data about defined events into a file. Profiler is a GUI that can be used to define a trace and view the results. When someone speaks about a "server side trace" that means that the trace is running on the server independently of Profiler. Profiler is a terrific tool but it does impose some additional overhead and it is not appropriate to run Profiler continuously on a production server. Many DBA's don't realize that by default there is a trace running all the time in SQL 2005. Just like in Profiler, a trace can be configured to capture as much or as little data as you want, also some of the events you could capture are going to impose much more overhead than others, so it pays to do your homework and define your trace as narrowly as possible but not narrower...Trace files are actually very easy to query with the fn_trace_gettable function. Part of the trace definition allows you to control how many trace files will be generated, how big they will be and whether they will be overwritten etc... so you can control the amount of disk space used.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 10:18:06 GMT

[quote][b]Jack Corbett (6/10/2008)[/b][hr][quote][b]DCPeterson (6/10/2008)[/b][hr]The most common problem I've seen when it comes to audit logs is that the requirements aren't well defined so you get something like this: "well, we don't know what we might want to see, so just capture everything..." Sure, I can do that, but then you will be buried under a mountain of useless audit data.[/quote]This is the problem. Most audit trails created audit everything and then expect you to sort it out.Now I am not advocating that this as the solution, but theoretically you could run your server-side trace to log the desired (well-filtered) selects to a table and, using a trigger, service broker, or another custom service, re-run the query to get the results using an account that is ignored by the trace or when logged ignored by the application re-running queries. This takes care of missing ad-hoc queries. Of course now you have doubled the read load on your database.Needless to say this is an interesting problem to consider, especially since I don't know how you could deny rights to the DBA. Then you would also have to protect the logged data and would need to log access to the logged data? Seems like an endless loop:w00t:[/quote]Yeah, this seems like a requirement out of a DBA nightmare! I don't think there is a GOOD way to do something like this. It's just plain ugly, no matter how you approach it.

RE: Audit Trails and Logging Part II

Jack Corbett — Tue, 10 Jun 2008 09:33:29 GMT

[quote][b]DCPeterson (6/10/2008)[/b][hr]The most common problem I've seen when it comes to audit logs is that the requirements aren't well defined so you get something like this: "well, we don't know what we might want to see, so just capture everything..." Sure, I can do that, but then you will be buried under a mountain of useless audit data.[/quote]This is the problem. Most audit trails created audit everything and then expect you to sort it out.Now I am not advocating that this as the solution, but theoretically you could run your server-side trace to log the desired (well-filtered) selects to a table and, using a trigger, service broker, or another custom service, re-run the query to get the results using an account that is ignored by the trace or when logged ignored by the application re-running queries. This takes care of missing ad-hoc queries. Of course now you have doubled the read load on your database.Needless to say this is an interesting problem to consider, especially since I don't know how you could deny rights to the DBA. Then you would also have to protect the logged data and would need to log access to the logged data? Seems like an endless loop:w00t:

RE: Audit Trails and Logging Part II

GSquared — Tue, 10 Jun 2008 09:19:03 GMT

DCPeterson, on the subject of a "server side trace". I'm not clear on what you mean by that. Are you talking about something outside the database, or some feature of SQL 2005/2000 that I'm unaware of? (I've read that SQL 2008 will offer more built-in auditing features, but haven't had a chance to play with those yet.) Or do you just mean running Profiler constantly?Just running Profiler constantly will give you a log, but the files will quickly fill up a lot of disk space, and can't be queried the way log tables can (at least, not easily). If you have Profiler inserting into tables, the performance hit is just about the same as using triggers, as is the space used.Could be done. Like any of the other methods, it has advantages and disadvantages. I can't think of an easy way to query such data to readily get the complete history of a particular row in a particular table, to run undo commands, or to track workflow data for management reports. On the other hand, you could get performance data right in your audit log, which could be useful for other things.

RE: Audit Trails and Logging Part II

GSquared — Tue, 10 Jun 2008 09:15:45 GMT

[quote][b]Lee Forst (6/10/2008)[/b][hr]I would like to get a feel from the author and others about a different type of logging. One requirement I have seen is that because of privacy issues, data loss protection issues, and sometimes legal requirements, systems must log who has “seen” the data. Rather than logging INSERTS, UPDATES, and DELETES, this is targeted to logging SELECTS. For example, let’s say you have a web site that allows end users to search for patients. The end users have a few different search criterion to search by. And for example, let’s say a user searches for a last name of Smith. The requirement says we must log who caused the search, when the search was done, what the criterion was [u]and the log the results returned to the application[/u]. In this case assume 10 fields were returned and 500 patients matched the last name of Smith. Therefore our log tables would need to contain that user John Doe on 06/10/2008 at 8:45am did a search of LastName = Smith and 500 records were returned by the search and record what data was returned (10x500).I think this type of requirement is very difficult to implement by using custom code and not to mention the storage required to do this. But in some cases it is law. Thoughts on the subject?[/quote]It is possible. There are a couple of ways to do it. One would be to set up something in the data connection layer that tracks that kind of thing. Another would be to add logging to your select procs, and then make sure all data is through those procs.A select proc could either use system_user or an input parameter to log who selected what, could certainly insert the input params and run-time into a log, and could, probably using For XML, log the results. It would take some work to build such a log, but not a lot, with For XML and an XML data type for the results log column. Would be much more complex to build with logging each column as its correct data type, or something like that.The main thing, though, is that this would not log if a DBA bypassed the procs and ran a direct select against the database, through the command line or Management Studio or some such. You would have to make sure nobody had that kind of access to the production database.I haven't had to set up anything like that kind of security, but I think it could be done.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 09:11:02 GMT

[quote][b]Jack Corbett (6/10/2008)[/b][hr][quote][b]DCPeterson (6/10/2008)[/b][hr]Now, tracking which queries were sent, which tables/columns were queried, and who sent them makes sense and is easily accomplished with a trace.[/quote]This is why I did not mention server side traces. Even running constant server side traces and storing in a table for querying will lead to a very large database quite quickly.[/quote]True, but an audit on a busy system is likely to become large regardles of the method used to create the audit. I have yet to see a system where the audit requirement stated that every time any user accesses any data, we have to track it. There is almost always some data that is sensitive and some that is not. Usually, you want to audit any access to the sensitive data and then audit changes to certain data. Traces can be tarteted (filtered) to capture data for only those events/objects/users that you are interested in.The most common problem I've seen when it comes to audit logs is that the requirements aren't well defined so you get something like this: "well, we don't know what we might want to see, so just capture everything..." Sure, I can do that, but then you will be buried under a mountain of useless audit data.

RE: Audit Trails and Logging Part II

Jack Corbett — Tue, 10 Jun 2008 08:55:11 GMT

[quote][b]DCPeterson (6/10/2008)[/b][hr]Now, tracking which queries were sent, which tables/columns were queried, and who sent them makes sense and is easily accomplished with a trace.[/quote]This is why I did not mention server side traces. Even running constant server side traces and storing in a table for querying will lead to a very large database quite quickly.

RE: Audit Trails and Logging Part II

DCPeterson — Tue, 10 Jun 2008 08:32:53 GMT

These were a pretty good basic overview of two of the options available. I think there is one option that was left out that offers much more flexibility than reading the transaction log with much lower overhead and lower chance of introducing errors than triggers. I'm talking about server side traces. Reading the transaction log won't show you any information about who selected from what table etc... This is critical for many databases that store sensitive data. Using stored procedures and triggers is cumbersome, error prone and imposes potentially significant overhead on your transactions. But setting up a server side trace is efficient (as long as you don't go crazy) can be tarteted to log just the operations against objects that you define, and is more difficult to circumvent than triggers. It also lends itself nicely to querying and analysis.Lee, I'm not aware of a solution that will actually record the result sets returned to a client, that seems like a ridiculous requirement. I've worked in credit card processing, banking, and insurance and I've never seen any audit requirement that is even close to that...I'm not saying that it couldn't exist, but man, that seems way excessive. Doing that it would be easy to imagine that the logging database would quickly dwarf the source. Now, tracking which queries were sent, which tables/columns were queried, and who sent them makes sense and is easily accomplished with a trace.

RE: Audit Trails and Logging Part II

Jack Corbett — Tue, 10 Jun 2008 08:17:05 GMT

Lee,I'm glad I'm not in that position. This impacts how you allow users to query the data as well. I would think the easiest way to implement this would be by using stored procedures for data access (which I would do anyway) and log within each stored procedure. Of course all the latest and greatest programming tools seem to use ORM, like Linq To SQL or SubSonic, and these tools all generate the selects, how do you log that? Now you have to add the audit code to the DAL. When doing that do I run the query twice, once to return it to the client and the second time to insert into the audit table, or do I do a batch update in the DAL using the dataset, list, collection in the DAL? I don't even know if these tools will do a set-based insert.

RE: Audit Trails and Logging Part II

LeeFAR — Tue, 10 Jun 2008 07:48:18 GMT

I would like to get a feel from the author and others about a different type of logging. One requirement I have seen is that because of privacy issues, data loss protection issues, and sometimes legal requirements, systems must log who has “seen” the data. Rather than logging INSERTS, UPDATES, and DELETES, this is targeted to logging SELECTS. For example, let’s say you have a web site that allows end users to search for patients. The end users have a few different search criterion to search by. And for example, let’s say a user searches for a last name of Smith. The requirement says we must log who caused the search, when the search was done, what the criterion was [u]and the log the results returned to the application[/u]. In this case assume 10 fields were returned and 500 patients matched the last name of Smith. Therefore our log tables would need to contain that user John Doe on 06/10/2008 at 8:45am did a search of LastName = Smith and 500 records were returned by the search and record what data was returned (10x500).I think this type of requirement is very difficult to implement by using custom code and not to mention the storage required to do this. But in some cases it is law. Thoughts on the subject?

RE: Audit Trails and Logging Part II

Jack Corbett — Tue, 10 Jun 2008 07:42:06 GMT

Nice series. I think the key is knowing what to log and why. I have been involved with logging where everything was logged because we could and I don't know that anyone ever looked at the logged data. I have also been involved with situations where changes were logged and used on a regular basis to show the "history" of a product.This is 1 area where I think DB2 at least on the AS400(iSeries) has SQL Server beat because you can query the journals (think transaction log) directly. It does store a limited amount of data, but in one place I worked we had a process that queried the journals and loaded a history of the product in SQL Server with virtually no impact on the DB2 database.

RE: Audit Trails and Logging Part II

GSquared — Tue, 10 Jun 2008 06:27:00 GMT

[quote][b]Anirban Paul (6/10/2008)[/b][hr]Nice one... I didn't expect the part II to come so quickly. How you people write so quick.....:)[/quote]I actually wrote the whole thing as one long article about a month ago. Steve had me split it in two. I can write pretty rapidly, but not quite like what you're seeing. :)

RE: Audit Trails and Logging Part II

Michael Earl-395764 — Tue, 10 Jun 2008 05:52:51 GMT

Nice article. I have done this a lot. Another option I don't know if you have considered is using transactional replication. It is nice because it splits the logging from the transaction, but it can still be nearly real-time in a lot of systems.Setting it up is pretty easy - just configure replication taking all of the defaults, and then modify the stored procedures it creates at the subscriber. There are a few snags to deal with, but it works pretty well overall.

RE: Audit Trails and Logging Part II

Anipaul — Tue, 10 Jun 2008 05:07:33 GMT

Nice one... I didn't expect the part II to come so quickly. How you people write so quick.....:)

RE: Audit Trails and Logging Part II

gideon.kahl — Tue, 10 Jun 2008 04:00:25 GMT

Another way to keep the log database "small":When I log workflow activity I normally decide how long the log data will be stored. Say I have a limit of 14 days: any log record older than 14 days will be deleted.Since I use my workflow activity logs for debugging interfaces, I don't need to keep the logged data for longer than that.

RE: Audit Trails and Logging Part II

hmi — Tue, 10 Jun 2008 01:23:02 GMT

Good article, by coincidence I wrote a script recently that generates three triggers for every table in a database for tracking change history of tablecontents into one single _Changehistory table using the xml datatype.This script should enable you to implement data change auditing with little effort. Please read preconditions and limitations.-- ==========================================================================================-- Author: Hans Michiels-- Create date: 4 June 2008-- Description: -- Generate triggers to detect inserts, deletes and updates in a database-- PRECONDITIONS:-- * Tablenames do not contain spaces-- * A table _ChangeHistory exists in your database, if not this script will create this table.-- * All tables have a single column that is primary key or has a unique index, if not a column -- called 'id' will be added to the table by this script-- * After schema changes in your database (e.g. new columns or tables) you should rerun this script to-- make sure that the triggers contain these new columns and new triggers are made for new tables.-- LIMITATIONS:-- * Impact on performance has not been tested, be careful when using this in a production -- environment!-- * Changes of columns of type text, ntext and image are not stored in table [_ChangeHistory]-- * It seems like only first 8000 characters of changes of columns of type varchar(max), -- nvarchar(max) and varbinary(max) are stored in table [_ChangeHistory], but this needs more testing.-- ==========================================================================================DECLARE @Cursor1 CURSORDECLARE @Cursor2 CURSORDECLARE @Tablename nvarchar(255)DECLARE @SQL nvarchar(max)DECLARE @SourceDb nvarchar(50)DECLARE @KeyColumn varchar(50)DECLARE @ColumnName varchar(50)DECLARE @ColumnNames varchar(max)DECLARE @SQLHead nvarchar(255)DECLARE @Newline varchar(2)DECLARE @ColIndent varchar(8)SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER ON;SET ANSI_PADDING ON;Use YourDatabaseNameHereIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[_ChangeHistory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[_ChangeHistory]( [ID] [int] IDENTITY(1,1) NOT NULL, [TableName] [varchar](50) NOT NULL, [Action] [char](6) NOT NULL, [DateCreated] [datetime] NOT NULL CONSTRAINT [DF__ChangeHistory_DateCreated] DEFAULT (getdate()), [KeyValue] [varchar](255) NULL, [DeletedRecord] [xml] NULL, [InsertedRecord] [xml] NULL, [Username] [varchar](50) NOT NULL CONSTRAINT [DF__ChangeHistory_Username] DEFAULT (user_name()) ) ON [PRIMARY];SET @Newline = CHAR(13) + CHAR(10) SET @ColIndent = SPACE(8)SET @SQLHead = '-- ============================================='+ @Newline + '-- Author: Hans Michiels'+ @Newline + '-- Create date: ' + CONVERT(nvarchar(25), getdate(),103)+ @Newline + '-- Description: This trigger is generated with a'+ @Newline + '-- script. Do not modify it by hand.' + @Newline + '-- ============================================='SET NOCOUNT ONSET @Cursor1 = CURSOR STATIC FOR SELECT CONVERT(varchar(255), so.name) AS tablename FROM dbo.sysobjects AS so WHERE so.type = 'U' and so.name <> '_ChangeHistory' -- and so.name = '_TestTypes' ORDER BY so.nameOPEN @Cursor1FETCH NEXT FROM @Cursor1 INTO @TablenameWHILE @@FETCH_STATUS = 0BEGIN --\ ---> Get the primary key field name --/ SELECT @KeyColumn = NULL; SELECT @KeyColumn = c.name FROM sys.objects AS o INNER JOIN sys.indexes AS i ON o.object_id = i.object_id INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id INNER JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id WHERE (o.type = 'U') AND (o.name = @Tablename) AND ((i.is_primary_key = 1) OR (i.is_unique = 1 AND i.ignore_dup_key = 0 AND i.is_unique_constraint = 1)) ORDER BY i.is_primary_key DESC, i.type IF @KeyColumn IS NULL BEGIN -- If the table has no primary key, a column named 'id' will be added as primary key. It is defined as IDENTITY (autonumber) SELECT @SQL = 'ALTER TABLE [dbo].[' + @Tablename + '] ADD id INT IDENTITY CONSTRAINT PK_' + @Tablename + '_ID PRIMARY KEY' EXEC sp_executesql @SQL SELECT @KeyColumn = 'id' END --\ ---> Cursor2 for building string with all fieldnames. --/ SET @Cursor2 = CURSOR STATIC FOR SELECT c.name as columnname FROM sys.objects AS o INNER JOIN sys.columns AS c on o.object_id = c.object_id WHERE o.name = @Tablename AND o.type = 'U' AND (c.system_type_id <> 34) -- image } These types cannot be used as source AND (c.system_type_id <> 35) -- text } column in SQL statement that uses AND (c.system_type_id <> 99) -- ntext } FOR XML AUTO ORDER BY c.column_id OPEN @Cursor2 SET @ColumnNames = '' FETCH NEXT FROM @Cursor2 INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ColumnNames = @ColumnNames + ', [' + @ColumnName + ']' + @Newline + @ColIndent FETCH NEXT FROM @Cursor2 INTO @ColumnName END DEALLOCATE @Cursor2 -- Remove first comma: SELECT @ColumnNames = SUBSTRING(@ColumnNames, 3, 9999) --\ ---> UPDATE trigger - DROP current --/ SELECT @SQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trgChgHis_' + @Tablename + 'Update]'') AND OBJECTPROPERTY(id, N''IsTrigger'') = 1) DROP TRIGGER dbo.trgChgHis_' + @Tablename + 'Update' EXEC sp_executesql @SQL --\ ---> UPDATE trigger - CREATE new --/ SELECT @SQL = @SQLHead + @Newline + 'CREATE TRIGGER dbo.trgChgHis_' + @Tablename + 'Update ON dbo.' + @Tablename + ' AFTER UPDATE AS ' + @Newline + 'BEGIN ' + @Newline + ' SET NOCOUNT ON;' + @Newline + ' INSERT INTO [dbo].[_ChangeHistory]([TableName],[Action],[KeyValue],[DeletedRecord],[InsertedRecord])' + @Newline + ' SELECT ' + @Newline + ' ''' + @Tablename + ''' AS [TableName]' + @Newline + ' , ''UPDATE'' AS [Action]' + @Newline + ' , CONVERT(varchar(255), [' + @KeyColumn + ']) AS [KeyValue]' + @Newline + ' , (Select ' + @Newline + ' ' + @ColumnNames + 'From deleted as ' + @Tablename + 'Record' + @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [maindel].[' + @KeyColumn + ']' + @Newline + ' FOR XML AUTO, TYPE, ROOT(''Deleted'') ) AS [DeletedRecord]' + @Newline + ' , (Select ' + @Newline + ' ' + @ColumnNames + 'From inserted as ' + @Tablename + 'Record' + @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [maindel].[' + @KeyColumn + ']' + @Newline + ' FOR XML AUTO, TYPE, ROOT(''Inserted'') ) AS [InsertedRecord]' + @Newline + ' FROM deleted AS maindel;' + @Newline + 'END' EXEC sp_executesql @SQL --\ ---> INSERT trigger - DROP current --/ SELECT @SQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trgChgHis_' + @Tablename + 'Insert]'') AND OBJECTPROPERTY(id, N''IsTrigger'') = 1) DROP TRIGGER dbo.trgChgHis_' + @Tablename + 'Insert' EXEC sp_executesql @SQL --\ ---> INSERT trigger - CREATE new --/ SELECT @SQL = @SQLHead + @Newline + 'CREATE TRIGGER dbo.trgChgHis_' + @Tablename + 'Insert ON dbo.' + @Tablename + ' AFTER INSERT AS ' + @Newline + 'BEGIN ' + @Newline + ' SET NOCOUNT ON;' + @Newline + ' INSERT INTO [dbo].[_ChangeHistory]([TableName],[Action],[KeyValue],[DeletedRecord],[InsertedRecord])' + @Newline + ' SELECT ' + @Newline + ' ''' + @Tablename + ''' AS [TableName]' + @Newline + ' , ''INSERT'' AS [Action]' + @Newline + ' , CONVERT(varchar(255), [' + @KeyColumn + ']) AS [KeyValue]' + @Newline + ' , NULL AS [DeletedRecord]' + @Newline + ' , (Select ' + @Newline + ' ' + @ColumnNames + 'From inserted as ' + @Tablename + 'Record' + @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [mainins].[' + @KeyColumn + ']' + @Newline + ' FOR XML AUTO, TYPE, ROOT(''Inserted'') ) AS [InsertedRecord]' + @Newline + ' FROM inserted AS mainins;' + @Newline + 'END' EXEC sp_executesql @SQL --\ ---> DELETE trigger - DROP current --/ SELECT @SQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trgChgHis_' + @Tablename + 'Delete]'') AND OBJECTPROPERTY(id, N''IsTrigger'') = 1) DROP TRIGGER dbo.trgChgHis_' + @Tablename + 'Delete' EXEC sp_executesql @SQL --\ ---> DELETE trigger - CREATE new --/ SELECT @SQL = @SQLHead + @Newline + 'CREATE TRIGGER dbo.trgChgHis_' + @Tablename + 'Delete ON dbo.' + @Tablename + ' AFTER DELETE AS ' + @Newline + 'BEGIN ' + @Newline + ' SET NOCOUNT ON;' + @Newline + ' INSERT INTO [dbo].[_ChangeHistory]([TableName],[Action],[KeyValue],[DeletedRecord],[InsertedRecord])' + @Newline + ' SELECT ' + @Newline + ' ''' + @Tablename + ''' AS [TableName]' + @Newline + ' , ''DELETE'' AS [Action]' + @Newline + ' , CONVERT(varchar(255), [' + @KeyColumn + ']) AS [KeyValue]' + @Newline + ' , (Select ' + @Newline + ' ' + @ColumnNames + 'From deleted as ' + @Tablename + 'Record' + @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [maindel].[' + @KeyColumn + ']' + @Newline + ' FOR XML AUTO, TYPE, ROOT(''Deleted'') ) AS [DeletedRecord]' + @Newline + ' , NULL AS [InsertedRecord]' + @Newline + ' FROM deleted AS maindel;' + @Newline + 'END' EXEC sp_executesql @SQLFETCH NEXT FROM @Cursor1 INTO @Tablename ENDDEALLOCATE @Cursor1GO

Audit Trails and Logging Part II

GSquared — Mon, 09 Jun 2008 21:50:22 GMT

Comments posted to this topic are about the item [B]Audit Trails and Logging Part II[/B]