SQL Server Audit log - statement empty

Question

SQL Server Audit log - statement empty

Anna Johansson

SSC Enthusiast

Points: 197
More actions
February 16, 2018 at 2:25 am

#403117

We are setting up audits to log stored procedure execution in our web application.
When running a stored procedure from management studio the log includes "statement" which contains the nameo of the stored procedure AND which parameters (with values) that are sent to it.
However when running the same stored procedure via the web application the "statement" in the log is empty. so you can see that the procedure was executed but not with which parameters.
Does anyony knows why there is a difference? I'm running as the same sql user.
BR
Anna

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

You'd likely get better responses if you clearly identified how you are capturing such information and which "logs" you're talking about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLPirate SSCrazy Points: 2880 More actions · Answer 2

Anna Johansson - Friday, February 16, 2018 2:25 AM
We are setting up audits to log stored procedure execution in our web application.
When running a stored procedure from management studio the log includes "statement" which contains the nameo of the stored procedure AND which parameters (with values) that are sent to it.
However when running the same stored procedure via the web application the "statement" in the log is empty. so you can see that the procedure was executed but not with which parameters.
Does anyony knows why there is a difference? I'm running as the same sql user.
BR
Anna

I had the same concern when I was setting up my database audit for executions. It's because RPC statements are not logged as mentioned in the Considerations section of the documentation here:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

If you start a trace and look for the executions, you'll probably see that the one from your app initiates as RPC:Starting and the one from SSMS will be SQL:StmtStarting.

Anna Johansson SSC Enthusiast Points: 197 More actions · Answer 3

SQLPirate - Thursday, February 22, 2018 1:30 PM
Anna Johansson - Friday, February 16, 2018 2:25 AM
We are setting up audits to log stored procedure execution in our web application.
When running a stored procedure from management studio the log includes "statement" which contains the nameo of the stored procedure AND which parameters (with values) that are sent to it.
However when running the same stored procedure via the web application the "statement" in the log is empty. so you can see that the procedure was executed but not with which parameters.
Does anyony knows why there is a difference? I'm running as the same sql user.
BR
Anna
I had the same concern when I was setting up my database audit for executions. It's because RPC statements are not logged as mentioned in the Considerations section of the documentation here:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions
If you start a trace and look for the executions, you'll probably see that the one from your app initiates as RPC:Starting and the one from SSMS will be SQL:StmtStarting.

Thank you very much for your answer. How did you solve this? Is there another way to do it?

BR Anna

SQLPirate SSCrazy Points: 2880 More actions · Answer 4

Anna Johansson - Tuesday, February 27, 2018 4:24 AM
Thank you very much for your answer. How did you solve this? Is there another way to do it?
BR Anna

One option we considered was running a server side trace, but in the end we opted to make the logging in our applications more verbose and record parameter info directly from whatever application is running to its own logs.