When a SmartList returns no data and Dynamics GP does not provide an error message

One of the most common issues reported to the RSM Dynamics GP Help Desk is when a SmartList returns data for one or more users but not for others. Sometimes the report returns no data, but in many cases, the user receives an error message stating:

“You do not have security privileges to view all of the tables used in this SmartList.”

A simple online search will return numerous articles and blogs that will guide you through the steps to resolve this problem. The general recommended solution is as follows:

1. Verify that the user has the security privileges that are required to view the SQL tables in a SmartList.
2. Verify that the user has access to the table that is used for the SmartList object.

I will provide detailed information about this resolution in the sourced Reference Material section at the end of this blog, but first, I want to cover a related situation that a client experienced that isn’t widely discussed in the available online resources.

Client case

The client has multiple Dynamics GP companies, and someone had created a multi-company view and surfaced the data from that view in a SmartList. Sometime later, the client hired a new accountant and set up all of the SmartList security the user needed to access the multi-company SmartList as outlined in the reference material section of this blog. However, the new user was intentionally not given access to all Dynamics GP companies from the User Access Setup window, only a subset of the companies. When the new user attempted to run the SmartList it returned no data, and no error message either.

If the user would have received the security permissions error noted previously, we could have quickly pinpointed the problem and resolved using the steps outlined below. However, because there was no error displayed, we had to take a deeper look for the solution.

We found the issue could be resolved by giving the new user access to all companies on the User Access Setup window. However, that would violate the client’s security policies. The best practice solution was to write a new SQL view that returned data only for the subset of companies that a particular user was authorized to access.

In considering this client’s situation, I am adding a third item to the recommended solution provided online.

1. Verify that the user has the security privileges that are required to view the SQL tables in a SmartList.
2. Verify that the user has access to the table that is used for the SmartList object.
3. Verify that the user has access to all the companies used for the SmartList object.

Reference Material

Sources:

• https://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/
• https://support.microsoft.com/en-us/help/967731/error-message-when-you-try-to-view-a-smartlist-object-in-microsoft-dyn

Symptoms

When you try to view a SmartList object in Microsoft Dynamics GP or Microsoft Dynamics GP 2010, you receive the error message:

You do not have security privileges to view all of the tables used in this SmartList.

Cause

This problem occurs if one or more of the following conditions are true:

• You do not have the security privileges that are required to access the table that is used by the SmartList item.
• You created a SmartList Builder object by using an SQL table.
• You are running Microsoft Dynamics GP 10.0, and you are using Data Connections.

Resolution

To resolve this problem, verify the following based on the version of Microsoft Dynamics GP that you are using:

• Verify that the user has the security privileges that are required to view the SQL tables in a SmartList.
• Verify that the user has access to the table that is used for the object.

1. Open the Security Task Setup window.
   Dynamics GP  >  Tools  >  Setup  >  System  >  Security Tasks
2. Open an existing task or create a new task.
3. In the Product list, choose SmartList.
4. In the Type list, choose SmartList Object.
5. In the Series list, choose SmartList Objects.
6. After the SmartList objects appear in the Access List pane, click to select the checkboxes of the SmartList objects to which you want to grant access.
7. Click Save.
8. If you opened an existing task in Step 2, you can skip the rest of the steps below.  If you created a new task in Step 2, proceed to step 9.
9. Open the Security Role Setup window.
   Dynamics GP  >  Tools  >  Setup  >  System  >  Security Roles
10. Open an existing role or create a new role.
11. Find the task you create in Step 2 and select the checkbox next to it.
12. Click Save.
13. If you opened an existing role in Step 10, you can skip the rest of the steps below.  If you created a new role in Step 10, proceed to step 14.
14. Open the User Security Setup window.
    Dynamics GP >  Tools  >  Setup  >  System  >  User Security
15. Select the user and company.
16. Find the role you created in Step 11 and select the checkbox next to it.
17. Click Save.

SmartList Builder Security

1. On the Microsoft Dynamics GP menu, point to
   Tools, point to SmartList Builder, point to
   Security, and then click SQL Table Security.
2. On the left side, click the checkbox next to the database name for which you are receiving the error.
3. On the right side, click the checkbox next to the table/view name that is used by the SmartList Item.Note: You can also click Mark All to mark all the tables in the company database.

At RSM, it is all about our clients.  Our strong, client-centric approach differentiates us. We want to be your advisor of choice. To that end, we strive to understand you, your business, and your aspirations.  By sharing the ideas and insight of our most senior professionals, we bring our local and global knowledge and resources to your environment so you feel empowered to move forward with confidence.

This is the power of being understood. This is RSM. Contact our experts at RSM 855-437-7201, or contact the RSM Dynamics GP National Help Desk, call 888-678-5536.

Darren Hunt