Update statistics from stored procedure (needs alter table permission)

Today instead of the beautiful information our reports displayed this ugly error

  • An error has occurred during report processing. (rsProcessingAborted)
    • Query execution failed for dataset ‘StoredProcedure’. (rsErrorExecutingCommand)
      • For more information about this error navigate to the report server on the local server machine, or enable remote errors

If we had access to the report server machine this was easy to troubleshoot. But we don’t have access to the report server. We needed another approach.

Image courtesy of jesadaphorn / FreeDigitalPhotos.net
Image courtesy of jesadaphorn / FreeDigitalPhotos.net

Looking through the changes from last night we noticed a small performance enhancement. In a stored procedure that prepares the data for the reports we added the following line:

UPDATE STATISTICS [dbo].[Fact]

In the Microsoft documentation it says you’ll need alter table permission to use update statistics. So we added another grant for the reporting service account.

GRANT EXECUTE ON [dbo].[proc_prepare4reporting] TO [SSRS]
GRANT ALTER ON [dbo].[Fact] TO [SSRS]

After the deployment the reports showed the beautiful information again.

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s