Granting access to a new SmartList in Dynamics GP

We have been seeing a lot of questions on granting access to new SmartList Builder SmartLists. Because this is so different from older versions of Dynamics GP, we’ve created a really detailed list to go through it. This may be overkill for some, but it will hopefully cover every needed scenario. (Update: These instructions will work for GP 10.0, 2010, 2013, 2015 and 2016.)

If you have not already done so, open SmartList – you will see a message saying SmartList Builder has detected changes. Click Yes.

74 Responses to “Granting access to a new SmartList in Dynamics GP”

Appreciating the time and effort you put into your blog
and detailed informatiion you present. It’s nice
to come acrosds a blog every once in a hile that isn’t tthe same old rehashed information. Excellent read!
I’ve saved your site and I’m adding yoour RSS feeds to my Google account.

Hi Victoria – Here’s another tip in case people are getting freaky with cross-database views in their smartlists: If you’re pulling in data from other databases into your view, you have to 1) add the GP user you want to run the smartlist as a user in the other database and 2) grant that new user SELECT access to all the tables in that other database that are being called in the view.

I spent a good couple of hours going through the steps on your blog before this finally clicked for me.

Victoria, I’m stumped with Smartlist security; I have a user who has assigned a role with the Defaultuser task which in turn has the Smartlist Builder window in the task. The user can see/run smartlists but when he clicks new he gets the error “You don’t have security priviledges to open this window…” everyone else seems to have access, any ideas?

Have you ever seen an issue where a SL is created using SLB. It’s a simple select from a custom table in GP and the DYNGRP has rights to this table yet when users run the SL, they get no results and no errors. The only user that gets results is “sa”. They are on GP 2010. I am completely baffled by this. I have went through SL security and also ran the grant script against the GP db that the table resides in. any help/guidance would be greatly appreciated.

I’ve never had an issue with this in GP 2010 and I have created probably hundreds of SmartLists, all based on SQL tables or views. If sa can see the data and other users cannot, it’s probably a SQL permissions issue. I would re-run the grant, maybe specifically against that table: grant select on YourTable to DYNGRP.

Are the SmartLists you want to give your users created in SmartList Builder? If so, it would need to be installed on every computer where you want to be able to see these SmartLists.

To grant access to everyone, you can follow the steps in this blog post and add your SmartLists to the DEFAULTUSER Task. That is typically something every user has already. If not, you can create a new task and role for this and assign it to every user. (Or assign the DEFAULTUSER Task to every user.)

I think that you found the right table, however, you do not need to add records to it – there should only be one record per SmartList Favorite. I have not thoroughly tested this, but it seems to work on a couple of SmartLists that I looked at. After making a backup of your data (!!) and closing the SmartList window run this against the DYNAMICS database:

I guess I misunderstood what you meant, I thought you wanted to make your SmartList favorites all set to ‘system’ instead of one particular user. That’s what the table you’re looking at holds – the SmartList favorites settings. This actually is not security – if someone does not have access to the SmartList, it does not matter what the favorite settings are.

Since it seems I misunderstood, if you could go into more detail on what exactly you have set up vs. what you are looking to achieve, that might be the better next logical step.

For SmartList objects you can specify what users have access to them using GP security. For SmartList Favorites you have 4 options:

User ID – the user that created the SmartList (in all companies)

User Class – all users in the same class as the user who created it

Company – all users in the company you’re logged into when the Favorite is created

System – all users in all companies

SmartList Favorites still follow the security permissions of the SmartList object they are under – if a Favorite is saved with ‘System’ and you do not have security permissions to the SmartList object, you will not be able to see it.

Normally, you can log in as the user that has the Favorite saved under their ID and change the SmartList to another option. The SQL script I originally gave you should accomplish changing the favorites to ‘system’ so that all users will be able to see them. Again, this will not change security to SmartList objects, so only the users that currently can see those objects will be able to see these favorites.

When I am in the SmartList Builder it does not show me the built in SmartLists, only the ones I have created. I would like to use one of the built in SmartLists as a basis for a custom SmartList. Is there a way to import/copy a built in SmartList. or Is there a way to see what tables are being used in the built in SmartLists?

What you are seeing is by design. You will not see ‘out-of-the-box’ SmartLists in SmartList Builder. These SmartList templates on CustomerSource may be helpful: https://mbs.microsoft.com/customersource/support/downloads/reportslibrary/gp_smartlist_templates.htm. There is also a list of the tables and joins used in the IG.pdf file that used to be on the 2nd installation disk for older versions of GP (9.0 and earlier). If you still have one of those, that might help, however, I typically found this list to be too ‘simple’ to be very useful.

I use SLB for a number of user-types in some of my clients installs. I’ve found it helpful to have one task called SmartListPermissions where all seven of the options are enabled. Then that task is assigned to a role called SmartListPermissions. When I want a user to have those permissions I assign the SmartListPermissions role to them.

If I wanted everyone to have those permissions I’d probably link the task to the DefaultUser role and be done with it.

Now, the slicy-dicey trick is how to create SLB security for different ‘types’ of users.

I do this in a similar manner with a task called SLB_PR_Users and (if it’s necessary to separate them) I create one for SLB_HR_Users. And, if the company has wide set of permissions in the accounting department I’d create a third task called SLB_Acctg_Users, and add all of these that to a Role with the same name.

Then, when I’m setting up SLB security for users I can assign SLB_PR and/or SLB_HR and/or SLB_Acctg to a user and know exactly what I’m giving them.

Assigning SLB tasks to pre-defined GP Roles muddies the water for me as I tend to forget which *Role I’ve added SLB permission to.

I am not sure you can see not GP databases directly in SmartList Builder. I would recommend creating a SQL view in one of your GP databases that points to the data you need in your external database. (Make sure that DYNGRP has permissions to the view.)

One other consideration – if this external database is not in the same SQL Server instance as GP, you may not be able to do this directly, as there is a Dexterity limitation that prevents this from working.

Hello Victoria:
Many thanks for your response.
Firstly, my external data bases resides on the same server. I can see the databases within SLB, however I cannot select the tables within the security window.

Hello, I’m wondering if anyone ever figured out the trick to being able to report off external databases within SLB?
Good news is that I know it CAN be done since I’ve done it with some databases already. However, I now have a new report I’m trying to make on yet another database and I am unable to see the tables within this database. I can see database ‘C’ in SLB security but none of it’s tables will list for me.

Here is a posting on cross database security with an interesting topic about ‘chaining’. Makes sense that the Users really need to exist in the databases. When creating your view in SQL as ‘sa’ the results will return data but when querying that view from SLB your ‘user’ needs to exist across all databases. You’ll need to make a decision on if creating a DYNGRP in your external database makes sense and then add users to that group OR decide which users need access to this information and add them explicityly to your external db with appropriate permissions – generally select. Not sure what the rest of the community thinks but cross database chaining on the heels of DYNSA as dbo makes me a bit nervous. It’s a case by case decision but again, this article may help.http://www.nextecgroup.com/portals/0/white%20papers/smartlist%20builder%20-%20cross%20database%20queries.pdf

I want to close smart list to appear at all for some users, I create Security task and not add smart list, and then create security role , but still users can view. Sales , sales item lines, sales transactions in smart list.

kindly help me to remove smart list from (Microsoft Dynamics GP menu). or when they make write click on home panel from the main GP application window.

Unless you are using a General Ledger SmartList, you cannot bring in actual account numbers used on a transaction. So if you need payables transaction information with GL detail, I would recommend starting with the view I mentioned in my last reply.

Second, for SmartList Builder, in GP 10 how do you make SLB available to others users besides sa? When I log in as anyone else other than sa I cannot go to DYNAMICS GP>TOOLS>SmartList Builder. Only sa can get in but I want other users to be able to create smartlists, is this possible and if so how?

Likewise for custom SmartLists that I have created as the sa user no one else can see them even though I have given them access to all SmartLits Objects as you described above.

Hi Victoria, I have created a View and follow all the steps in here and it is working fine on me (as power users).
And I have add it on SmartList for a User.
The User able to see the SmartList but there is no Data appear.

That may very well be. I know there were a lot of things fixed post SP 1. Unfortunately I don’t have any easy way of testing this quickly. If you need to test out that theory (as opposed to just updating to SP 3), perhaps you could post this question on the GP Newsgroup.

I just tested this in GP 10.0 SP 3 with a user who is not a Power User and had no problem at all getting to the Calculated Fields window. Here is what I think you need to give them access to: Under Security Tasks, change Product to SmartList Builder, change Type to SmartList Builder Permissions, change Series to SmartList Builder, under Operations there is a choice for Enter Calculated Fields. Realistically, anyone creating SmartLists should probably have everything allowed under all 7 of the sections listed a few comments up (dated 2009/03/23 at 12:56pm).

Hi I was wondering if you could help for a min. I have followed you outline here and I am still having trouble with SL builder. I have created the security task called “Smartlist” but what is the category that you used? I also created a security task called smarlist builder. I have company in there now for the category.
Product-Smartlist builder
Type-Smartlist builder permissions
Series-Smartlist builder

I have then added these security task to a security role but the problem is under Tools there is not Smartlist builder listed except for SA.

The instructions in this post are really for granting someone access to a SmartList already created with SmartList Builder, not the SmartList Builder tool itself. Typically the users creating SmartLists are going to be POWERUSERs in most companies, so this probably doesn’t come up very often, but if you want to give someone that’s not in POWERUSERs access to SmartList Builder: Create a new task, I would use the System Category (although it really doesn’t matter, that is just something that helps you find things a little easier), then go through the following combinations and choose what you need:
1. Product: SmartList Builder, Type: Windows, Series: Project
2. Product: SmartList Builder, Type: Windows, Series: System
3. Product: SmartList Builder, Type: SmartList Builder Permissions, Series: SmartList Builder
4. Product: SmartList Builder, Type: SmartList Builder Permissions, Series: Excel Report Builder
5. Product: SmartList Builder, Type: Reports, Series: System
6. Product: SmartList Builder, Type: Files, Series: System
7. Product: SmartList Builder, Type: Files, Series: Project

Another gotcha with SLB objects based on SQL views – the SQL Table Security is not transferred within the SLB export.

I have just completed an upgrade where we had a version 10 test system on which we had created SLB objects.
During the production upgrade I was able to transfer all objects – however when I attempted to use the Smartlist with those objects I got the message ‘you do not have security to access all tables for this smartlist’.

Investigation showed the rows necessary for SQL table security was not transferred.
Using SDT (Support Debugger Tool) I exported the contents of SLB80300 and SLB80400 tables from the test system and imported into the production system.

The SLB object based on SQL views now worked flawlessly.

So, if you transfer an SLB object between different systems in version 10, and these are based on SQL tables or view, you need to also redo the SQL security by some mechanism ! ( Tools –> Smartlist Builder –> Security –> SQL Table Security )

At what exact point are you getting the error ‘you do not have security to access all tables for this smartlist’? And as what user? Typically the issue is either a user can see the SmartList, but it returns no data, or the user cannot see the SmartList at all. I don’t usually see too many error messages with this.

I have done all the steps to push securities out but I am getting an error ‘you do not have sercurity to access all tables for this smartlist’. The smartlist is from a view. SA can see it, power users can get to it, but that is it. I have granted on the view, set up security through GP. What did I miss?

Question regarding smartlist builder.. helped a client create a smartlist so he could get all the data he was looking and we added in a few columns to the purchasing -> payables transaction -> * and for some reason not all the Purchases Account Numbers were listed. (Purchases Account Number was a column we added). Can you help me figure this out on why not all the purchaes acct # were not there?

The Purchases Account Number column may not be what you think it is. Typically, it is the Purchases Account set up for the Vendor on the Vendor Account Maintenance window and may be completely different than what is on the actual transaction for that vendor. If you’re looking for the GL distributions of payables transactions you might want to start with something like this view: Payables GL Distributions.

Victoria,
Where are the Smartlist favorites saved to? Are they in the same EXT/SLB tables as the other Smartlist or someplace different? I’m wanting to make sure these favorites get backed up in case of emergency.