In my workplace we use the reports based off the SSRS Reporting Services database ExecutionLogStorage to identify report usage and alert us to report execution failures, in the form of the below statement to identify how often different reports have been used, how long they take to run, and how often they fail:
select Catalog.path, ExecutionLogStorage.*
from ExecutionLogStorage
inner join Catalog
on ExecutionLogStorage.ReportID = Catalog.ItemID
Unfortunately, on a server that went online some-time last year, the report execution logs are very limited. After doing some research I found the answer here (Microsoft TechNet article).
If the query below returns anything other than -1, your report execution logs will be being deleted after the resulting number of days.
select *
from ConfigurationInfo
where name = 'ExecutionLogDaysKept'
If it is, update it to -1 to stop historical logs from being deleted.
As to how to restore the deleted ExeuctionLogStorage entries, you'll need to restore a copy of the database to a temporary location, and run insert operations to re-add those rows to the table.
Tuesday 17 February 2015
Saturday 31 January 2015
Users cannot search for Reports on SSRS 2012 even with the System Administrator role
Today I encountered an issue where domain users could not search for reports on a recently comissioned SSRS instance. The users had the System Administrator roles in SSRS, but received the error “The permissions granted to domain\username are insufficient for performing this operation. (rsAccessDenied)” when attempting to search or access the Reporting Services home page (http://reportserver/reports). When the user was added to the server’s local Administrator group (BUILTIN\Administrators) the issue did not occur.
The Reporting Services log file had an error beginning with the following added:
“Microsoft.ReportingServices.UI.FolderPage+InsufficientPermissionsToRoot: User domain\username does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed. at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)”
Disabling UAC, as blog posts suggested, would require a restart to check, and the previous server had UAC set to its default and was working as expected.
The users had browser role on each of the folders on the home page.
The solution was to give the users the browser permission on the home page. Turns out, that home page is a folder, with its own security, and without the browser role on the Home folder, they can’t view the home page or search for reports.
The Reporting Services log file had an error beginning with the following added:
“Microsoft.ReportingServices.UI.FolderPage+InsufficientPermissionsToRoot: User domain\username does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed. at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)”
Disabling UAC, as blog posts suggested, would require a restart to check, and the previous server had UAC set to its default and was working as expected.
The users had browser role on each of the folders on the home page.
The solution was to give the users the browser permission on the home page. Turns out, that home page is a folder, with its own security, and without the browser role on the Home folder, they can’t view the home page or search for reports.
A period is numeric...
When using ISNUMERIC(<field name>) as a filter condition, remember that if your field contains a single period ( . ), it will be accepted as a valid numeric value (0.0).
Subscribe to:
Posts (Atom)