Light weight SQL Server procedure auditing without using SQL Server auditing

(UPDATE: Updated technical version is available through this blog post here . The basic information and background though is available in the blog post you are reading)

A week ago a colleague asked for different options to do audit stored procedure calls. With his allowance I will post the question here.

“The applications used at the customer site all access these databases using stored procedures (only, as far as I know) and are written using .NET.

The customer is interested in increasing the amount of logging they are doing to capture the syntax that is hitting the databases.

Specifically, the customer is interested in capturing RPC events that will show them the parameters being fired at the databases, e.g. someSproc ‘1’,’2’,’3’, someSproc ‘2’,’2’,’2’, someSproc ‘3’ and so on.

The customer intends to store the syntax in a database for analysis.”

So the overall question was, how can we do auditing of the procedure calls. My answer to this was:

“Without putting the load for logging on the server the customer could implement another stack in their application doing logging before actually doing the execution of the procedures. If they are not sure if only the application is doing executions against the database, bypassing the new logging stack or they cannot change the code you will have to do the logging at the server. Depending of the SQL Server version you have several options:

• SQL Server 2000/2005:
  • Do a server side tracing, be aware that this is not a light weight option to do
  • Change your stored procedures to include the logging of the execution
• SQL Server 2008
  • The two options from above
  • Using extended events (sqlserver > Analytic > execution > rpc_starting) (But they have not the option to track the parameters)
  • Using SQL Server Audit (Depending on how they execute the procedure the parameters might not be tracked)"

“

So boiled down and due to the problem that the customer used SQL Server 2005, there was not much option to do. SQL Server 2008 could have been used with the new auditing functionality, but the version upgrade was out of scope and another solution need to be found.

So we headed for the second option, the “Change your stored procedures to include the logging of the execution”. I suggested him to add something in the stored procedures catching the execution of the procedure as well as the passed parameters. Well, having hundreds of procedures as in this situation, its really hard to maintain and implementation and can get very tedious. An automatic solution had to be done to implement logging and to honor the different parameter loggings, as well as to be able to change that after once implemented if e.g. the parameters or the calling logic to the log procedure changes.

The attached solution will do the hard work for you, identify the part in the stored procedure where the logging is done (immediately after the AS statement in the stored proc), remove any logging (if already inserted earlier and you might changed the logic of the logging) and add the appropriate logging part. Open the solution and go through the steps mentioned there 1-6. It is really easy and easy to apply to your existing logic. You will get all the benefits of knowing which procedure was called, when, from whom with which parameters. Be ware that due to privacy concerns you might not be allowed to gather all the personalized data and might need to change the procedure which does the logging and the persistence to the database.

Feel free to give some feedback about the lightweight way of adding logging to your database, I would really appreciate it (either good or bad :-) )

 

-Jens

ProcedureLogging_v0_3.zip

Comments

• Anonymous
  July 09, 2010
  Hi Jens, This is something I was looking for. Thanks a lot - this covers 99% of the auditing and debugging needs. Although I have took a liberty of modifying your code slightly, if you don't mind. Now it allows to save XML with the list of parameters. Let me know, if you would like me to send it to you, so you could provide the updated version, if you find it worth the effort.

• Anonymous
  April 10, 2011
  The comment has been removed

• Anonymous
  April 10, 2011
  Hi Tim, unfortunetaly you didn´t leave an EMail adress therefore I will answer right here. Did you consider the next version of the procedure logging ? it´s located here: blogs.msdn.com/.../light-weight-sql-server-procedure-auditing-without-using-sql-server-auditing-version-2.aspx There are currently problems with table values parameters which I didn´t fix yet. So, if you have any parameters of this kind, it will simply fail to apply the logging. I appreciate any samples of code where this does not work in order to harden the code for applying logging. Feel free to drop me a mail to JensS(@t)microsoft.com -jens

• Anonymous
  April 10, 2011
  Thank you very much Jens I really appreciate the quick response. If I understand correctly some parameters would cause the logging to fail? What if I don't really capture the parameters passed to each stored procedure, would that solve the problems I am having? All I am interested in is the ability add a log to the LogTable everytime a stored procedure is called. The values I will be storing are. DatabaseName, ProcedureName, CallingTime, and CallingUser. These are pretty much all the info I need as part of the audit. my email addres is tbensehil!yahoo.com Thank you again

• Anonymous
  April 10, 2011
  Also I tried downloading the other version of auditing but I can't open it since I only have SQL Server Express Edition. Could you please email me the actual logging script files or direct me to a URL? Thank you tbensehil@yahoo.com

• Anonymous
  September 03, 2012
  The comment has been removed