I found the definitive answer!!!
1. You can only create\publish an Excel report from a SmartList Designer query that has been built using a SQL view. If you've used SQL tables in your SmartList Designer query you will not be able to publish it to an Excel report. This is the first version of this new feature so we expect it to be expanded beyond SQL views in a future update. You can use existing GP SQL Views or build your own.
2. The user who is attempting to publish must have Read\Write\Modify permissions to the share location where the Excel reports are deployed to.
3. The default Excel reports must be deployed already.
That is why users don't need extra security to Publish. Unlike SmartList Builder (Which creates a new SQL View), SmartList Designer requires an existing view. I stumbled into the answer because I created a new SmartList report based on a table and I could not publish!! So I searched the internet and found this answer!!
If you've found this thread useful, click here to dive deeper into User Group community content by role