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 :-) )