I was deploying Lync Server 2013 Monitoring Reports last week in a 2x Standard Edition deployment (pool paired – my architecture of choice for organisations under 2000 users) with a single SQL 2012 backend server. I deployed the reports like normal, went to access the URL and got this:

I was thinking “this doesn’t usually happen, I am perplexed. Monitoring reports are usually bulletproof!”.

The error read (for those that enter it into Google/Bing when this happens to them):

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘CDR_SyncTimeZone’. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object ‘RtcSyncTimeZoneInfo’, database ‘LcsCDR’, schema ‘dbo’.

Weird huh? We’d just installed the latest server cumulative update, so I tried reinstalling Monitoring Reports after I’d applied the CU database update also, but no dice. I started digging through stored procedures and permissions, but nothing was really jumping out at me. I thought this was something to do with SQL Server Reporting Services (SSRS) not being initialised properly or just not playing ball, so I reinstalled it and redeployed the reports, still nothing.

After some searching, I found a similar SSRS problem and worked out this was permissions related somehow.

In SQL Server Management Studio I went into the login properties for the account that has access on the Monitoring databases and gave it db_owner rights on both databases.

I went back to my browser, hit refresh on the Report Server URL and bam, nice properly functioning Lync 2013 monitoring reports.

Why things didn’t work out of the box, I don’t know. I don’t think there’s any risk in this account having db_owner rights on the Monitoring databases as it is just a service account, but it’d be worth documenting in your environment all the same.