[Updated]: Light weight SQL Server procedure auditing without using SQL Server auditing version 0.3.0.0

  • Article

 

(The new solution can be downloaded here)

(The detailed Information of the solution is mentioned here)

After having seeing the solution settled in numerous projects and getting great feedback from you, I added the following request to the solution.

Out-of-transaction support for Procedure Logging

While you might run the logging within your own crafted transactions, there might be situations where the transaction is rolled back due to a failure or within one of your test cases. In that case and because that the logging framework executes directly within the transaction context of the injected procedure, the log records won’t be preserved either. For this, I added the support of bringing in a linked server which is doing a non-participating transaction. The additional parameters which are outlined as below:

  • @LogServerName: Defines the linked server name where the log data is pushed to, the server need to exists upon the injection of the procedure code.
  • @LogDatabaseName: Defines the database name where the log data is pushed to, the database need to exists upon the injection of the procedure code.
  • @PassCurrentProcessUser: If you are using a linked server not with delegated credentials and are using a fixed mapped user and password, the identity would change when the logging is persited. Therefore you can specify if an extra parameter value should be passed, identifying the original caller of the procedure.

 

  • image

While this is open source, you can change the code on your needs. If you though have a brilliant idea on how to make things better, let me know and we will get this back in with credits to yours.

-Jens