SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Audit log - statement empty


SQL Server Audit log - statement empty

Author
Message
Anna Johansson
Anna Johansson
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 10
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860350 Visits: 47133
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLPirate
SQLPirate
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2198 Visits: 1561
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
Anna Johansson
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 10
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
SQLPirate
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2198 Visits: 1561
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search