PHISSUG

SQL Server 2005, you can't do this with SQL Server 2000.

You have a business requirement to audit all inserts, updates, and deletes from one or more tables within a database.  The additional technical requirements are as follows:

1. The audit data must be logged into a single table within SQL Server, regardless the number or structure of the tables being audited

2. You have to log the name of the table, the time of the change, the user making the change, the image of the row changed (the data as inserted, the before and after image for an update, and the data in the row which was deleted)

3. Each audit record has to contain a flag indicating what type of change was made

4. With the exception of a sysadmin and the database owner of the database being audited, no one should have the ability to directly select, insert, update, or delete from the audit tables.  Additionally, they should not even be able to view the definition of the audit tables or even know that they exist.

5. #4 has to be maintained even in the case where a DBA grants a user select, insert, update, or delete authority on the entire database being audiited.  #4 must be maintained without requiring the DBA to revoke permissions from any user.

6. The DBA can not inadvertently allow access to the audit tables through ownership chains.

7. You can not use any external code base such as a 3rd party utility, tran log analyzer, SQL Server Profiler, or custom code.  It has to be entirely self-contained within SQL Server and entirely implemented within T-SQL.

Since this is a real brain twister even if you've been using SQL Server 2005 for several years, let's up the ante a little.  The best answer by Oct. 19th gets a p100 load.  (Since I'm traveling on the 18th and 19th, it might take a couple of days to evaluate any last minute entries.)  The solution has to have demo code and an explanation of why the specific feature choices were made.  Enjoy!

---

President - FilAm Software Technology, Inc
http://www.filamsoftware.com

Bonus points are awarded if you can also answer:

1. How would you audit whether a database owner or sysadmin changed data within the audit tables?

2. How would you implement this same capability, except in a central location logged to from multiple SQL Server instances without using a linked server, enabling OPENROWSET/OPENDATASOURCE, or causing any performance impact for a transaction due to the fact that the audit record now has to move between servers?

---

President - FilAm Software Technology, Inc
http://www.filamsoftware.com

i've developed a sql server audit application very similar to this. however, instead of using triggers to audit DML statements (i think apex uses that approach), i use server side traces. it's less intrusive and doesn't require creation of additional trigger objects.

traces created by the application are set with a maximum file size of only 5MB and a maximum (rollover) file count of 2 trace files. then, an event notification for the TRACE_FILE_CLOSE event is created which executes a stored procedure that saves the contents of the recently closed trace file (using fn_trace_gettable) to an audit table.

in order to get relevant information other than the captured executed statements and affected objects (from the trace), the table returned by fn_tace_gettable is joined with sys.dm_exec_sessions (using the trace event's SPID) to get the user's login time. sys.dm_exec_connections is also joined (using sys.dm_exec_sessions' sesion_id) to get the user's public IP, local IP, domain name, domain user name and host (computer) name.

as for capturing the image of inserted/updated records. it's possible reading the active log file using fn_dblog(). however, (re)creating the exact row image (into human readable data), as i have discovered through reserach, requires painstaking work (which is why i didn't even attemp to include such feature in my application) and is practically impossible to do so using native sql server functionality and TSQL. for this reason, i've started to consider to implement Katmai's CDC in my app. the drawback is that it will only work with 2008 version.

as for requirements 4 & 5, they may be relatively easy to implement but i don't know exactly at this point how to do so.

requirement 6 can be implemented using a DDL trigger. my application actually audits both DDL and DML statements. object auditing and protection is easily implmented using a single DDL trigger.

if you or anyone else in this group is interested in my application or hooking up to further develop (and make money off of) it, please PM me. it's already commercially available in the market but current (publicly available) version generates very modest sales. i have a newer version which is already Katmai compatible but is still in beta. if anyone is interested in the beta, PM me. what i really need is someone whom i can collaborate with for this project. i think it has great potential in the market, considering that most similar tools sell for several thousand $. mine is currently sold for only $375. ultimately, for future (and improved versions), i intend to sell for just under $1000. as i am by profession a dba, a c# guru would be very helpful (i wrote the app in c#). of course, funding for the project would also be helpful. so if anyone is interested in any way, drop me a line.

100 peso load lang? hahah!! kaya ko to gawin sa SQL 2000 eh hehehe

---

Do what can't be Done.