This may be due to a connection failure, timeout or low disk condition within the database. For more information about this error navigate to the report server on the local server machine, or enable remote errors.

SQL Server 2008 R2/2012/2014: ReportServer error report Subscription error

This may be due to a connection failure, timeout or low disk condition within the database.
(rsReportServerDatabaseError)
For more information about this error navigate to the report server on the local server machine, or enable remote errors.

The EXECUTE permission was denied on the object 'sp_help_category', database 'msdb', schema 'dbo'.

As part of our new security policies, I had to revoke permissions from the PUBLIC group. Also, I had to revoke SYSADM authority from certain users. This is typical when the application group requests highest authority since they don't want to spend the time to find what permissions are actually required by the application.

After the permissions were revoked, the users reported that above errors when they were trying to add subscriptions to the reports in SQL Server Reporting services.

But the message didn't mention that the error was or what permissions were missing.. To identify the error you have to complete the following two steps to identify the error.

1) Set the 'EnableRemoteErrors' configuration in the ReportServer to TRUE. This will show the exact error in the error log file for ReportServer.

To do this connect to ReportServer instance from SQL Server Management studio and right click on the server and go to Properties.

Then go to the ADVANCED and set the parameter EnableRemoteErrors to True.

You can find the current value by running the following command against the database server.

Use ReportServer

select * from dbo.ConfigurationInfo

where Name ='EnableRemoteErrors'

Once you make this change, you might have to restart the Report server instance to complete the change. Now redo the task that you were trying to complete in the Reports page.

Now check the error file in the ReportServer error log directory.

This will display the exact message in the file. In our case, I saw the following two messages.

This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'syslogins', database 'mssqlsystemresource', schema 'sys'

This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_help_category', database 'msdb', schema 'dbo'.

This clearly shows what permissions are missing. However, we still don't know which user requires these permissions. So get that information run the SQL Server profile which is the second step.

2) Run the SQL Server Profiler to capture the exact message. But as always remember that SQL Server profiler is a very powerful tool and if you capture all the events then you can potentially bring down the server and also collecting too many events will make the analysis very difficult.

So open SQL Server Profiler and capture the event called 'User Error Message' in 'Errors and warnings'

Once you run the profiler, you can try to execute the same task in Reporting Services and try to recreate the error. In my case, I captured the event 'Audit Schema Object Access Event' but that didn't help much so you can avoid that event.

You can see the same error being reported here as well. If it was a permissions issue then for that given record you will see the value 229 in the column for 'Error' in profiler. Now look int he column for 'LoginName' and you should be able to find the name of the login that is missing that permission.

Lastly, grant that permission and see if that fixes the error. However, I always recommend to grant only the permission required and not dbowner or SYSADM.