Security Privileges in SmartList Builder
I’ve created a new SmartList Builder SmartList that is using a SQL View. I’ve run grant.sql on the customer database and made sure the table has DYNGRP permissions.
My users are all assigned DYNGRP roles, and they all have access to the report via a Security Task/Role. I’ve also verified the report works fine for the ‘sa’ user as well as other Microsoft Dynamics GP PowerUser users. Unfortunately, all other “typical” users get the following error message:
You do not have security privileges to view all of the tables used in this SmartList.
What’s going on? Are there any security privileges in SmartList Builder that I’m missing?
If you’d like to watch a video explaining this, see below or click here.
There are a few security scenarios that we can eliminate as the issue.
- The fact that the report shows up in SmartList means that there are GP Permissions set to this custom Smartlist – if not, it wouldn’t show in this list.
- If it was a SQL Permission to the view itself that was wrong, SmartList Builder would execute the report but quietly fail with SQL permissions failure, and the report would show no data and no errors.
- Another common security issue with SmartList Builder views is that SmartList Builder SQL Permissions haven’t been granted to the view. To resolve the error, in Microsoft Dynamics GP, navigate to Tools | SmartList Builder | Security | SQL Table Security. Make sure that for the SQL Table or View on the report, the database and the SQL object used are enabled as they are in the below screenshot. The fact that the report runs fine for the ‘sa’ or other PowerUser role users is another clue that it wasn’t this typical solution.
Since it is only standard users that receive the message, the only other reason for the error is that the permission to “View SmartLists with SQL Tables” isn’t set.
Once this permission is marked in a SQL Task and granted through a Role to the same user as before – in this case ptr2024 – we can see that the report runs successfully.
If you have questions regarding Security Privileges in SmartList Builder, contact support@eonesolutions.com.
