Permissions error exporting Microsoft Dynamic GP SmartList Object

Microsoft Dynamics GPA client recently reported a problem whereby users were no longer able to export a SmartList favourite to Excel as they were receiving a permissions error when the Excel file opened:

The SELECT permission was denied on the object 'uv_AZRCRVBudgetByMonth', database LIVE', schema 'dbo'

I did some checking and realised that the problem was that the file being opened when the user tried to do an export, was named like a Refreshable Excel report; a little more checking showed that there was a Refreshable Excel report of that name using the view from the SmartList. The SmartList object being used was using a SQL View to return the data and this had made the Publish button available. An accidental click had then published the Excel report, but, as the user did not have permissions to execute the view under their Windows account, the Excel report produced the error.

To confirm this I ran the below script to see what published Refreshable Excel reports were present:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT DEX_ROW_ID FROM syDeployedReports WHERE ObjectType = 5

In this case, a few items were returned, but none of the SmartList Designer SmartLists should have been published, so I was able to take the above script and include it in a delete statement:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DELETE FROM syDeployedReports WHERE DEX_ROW_ID IN ( SELECT DEX_ROW_ID FROM syDeployedReports WHERE ObjectType = 5 )

After running this, we just needed to delete the Excel report from the published location (same as the ones published from Reporting Tools Setup).

Now when a user tries to export the SmartList, the data is exported to Excel using the standard export function.