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



Comments (6)

  1. _RA_ says:

    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.

  2. Tim Ben says:

    I tried to implement your code to implement logging to my stored procedures, but It does not work for all of them, and errors are returned when you execute the dbo.usp_ApplyLoggingToStoredProcs. When I debug the stored procedure I see that the Logging part is inserted before the CREATE Procedure statement, which is why errors are returned, and hence the parsing does not get applied..

    I looked I the part that actually splits the Function and seems that the @Firstpart is Null when is.


    @Firstpart = LEFT(@ChoppedProcCommandText,CHARINDEX('AS' + CHAR(13),@ChoppedProcCommandText)+2),

    @Secondpart = RIGHT(@ChoppedProcCommandText,LEN(@ChoppedProcCommandText) – CHARINDEX('AS' + CHAR(13),@ChoppedProcCommandText)-2)

    SELECT @ChoppedProcCommandText = @Firstpart + @LoggingCommand + @Secondpart

    I tried to check when the @Firstpart is null and alter the code, but It does not seem to work. Any idea how I can work around this issue.

    Could you please help me. THis is great way of adding logging to SP.

    Thank you

  3. Jens Süßmeyer says:

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


  4. Tim says:

    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!

    Thank you again

  5. TIm says:

    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

  6. Sreeni says:

    How could i get select command start & end time(Means execution time of the query) using auditing in sqlserver-2008?