How do you handle logging using SSRS?

Question

I have reports in SQL Server Reporting Services 2005 in which I need to keep an audit trail. The audit trail should include who ran which report with which parameters. I can not use windows authentication.

What is the best way to record this information?

Answer 1

The previous comments were accurate, so you could extract data from the ReportServer ExecutionLog table in SQL Server 2000/2005 or the ExecutionLogStorage table in SQL Server 2008. If you use forms-based authentication to access reports instead of Windows, then you probably pass some Unique UserID, CompanyID, CustomerID, or other value as a parameter in their reports. If so, then the built-in table already fixes the parameters. If you do not pass a unique user ID as a parameter, you may have to rely on logging reports in the application itself.

Answer 2

See the ExecutionLog table in the ReportServer database. This contains information about who launched the report and with what parameters.

I'm not sure how this will work without Windows authentication, though, since it will have no way of knowing who is running which report.

Answer 3

Can you share information about the authentication method?

MS provides some sample reports that include everything you need to get started.

For SSRS 2005 http://www.codeplex.com/MSFTRSProdSamples/Wiki/View.aspx?title=SS2005!Server%20Management%20Sample%20Reports&referringTitle=Home

Many more reports. http://www.codeplex.com/MSFTRSProdSamples/

Answer 4

Out of memory SSRS has a built-in log for this exact situation

Answer 5

If you use your own security extension, you can still get all the necessary information from the ExecutionLog table. If not, if all your users use shared logins, in which case you may have to rethink your architecture based on the importance of the audit trail.