Granting access to a new SmartList in GP 10
October 8, 2008 36 Comments
We’ve been seeing a lot of questions on granting access to new SmartList Builder SmartLists in GP 10.0. Because this is so different in GP 10.0, 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.
1. If you have not already done so, open SmartList – you will see a message saying SmartList Builder has detected changes. Click Yes.
2. Open the Security Task Setup window.
Dynamics GP > Tools > Setup > System > Security Tasks
3. Open an existing task or create a new task.
4. In the Product list, choose SmartList.
5. In the Type list, choose SmartList Object.
6. In the Series list, choose SmartList Objects.
7. After the SmartList objects appear in the Access List pane, click to select the check boxes of the SmartList objects to which you want to grant access.
8. Click Save.
9. If you opened an existing task in Step 3, you can skip the rest of the steps below. If you created a new task in Step 3, proceed to step 10.
10. Open the Security Role Setup window.
Dynamics GP > Tools > Setup > System > Security Roles
11. Open an existing role or create a new role.
12. Find the task you create is Step 3 and select the check box next to it.
13. Click Save.
14. If you opened an existing role in Step 11, you can skip the rest of the steps below. If you created a new role in Step 11, proceed to step 15.
15. Open the User Security Setup window.
Dynamics GP > Tools > Setup > System > User Security
16. Select the user and company.
17. Find the role you created in Step 11 and select the check box next to it.
18. Click Save.
19. Reward yourself with a cookie. 
~~~~~
Update on Dec 9, 2008: If you are on GP 10.0 and your SmartList is based on SQL views or tables, you will need to add permissions to the View SmartLists with SQL Tables operation. Here are the settings under Security Tasks:
- Product: SmartList Builder
- Type: SmartList Builder Permissions
- Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
- Operations: View SmartLists with SQL Tables
This only has to be done once, no need to repeat if for every new SmartList. Fellow MVP Mariano Gomez has step by step instructions for this on his blog.
~~~~~
Update on Nov 25, 2009: Corrected navigation paths in steps 10 and 15. Thanks to Erik for pointing this out!
~~~~~
Update on Dec 29, 2009: Here is a great post by Christina Phillips at Dynamics GP Land that may help troubleshoot some permissions errors: SmartList Builder and SQL Tables/Views.
You get a cookie from me. Thanks! Different from v 9.
Hi Victoria,
Latest one to join the “Self-Rewarding a Cookie” bandwagon.
Thanks so much, it was such a simple security access thing that I missed. But it proved really critical at this moment.
Thanks again.
Vaidy Mohan
Hello Victoria:
Trying to create a smartlist, using SLB, with a table from Dynamics and an external database(mydatabase).
Tools –> SmartList Builder –> Security –> SQL Table Security
When I check mydatabase, I cannot see the associated tables or the views.
I have added the DYNGRP permission to mydatabase.
Please advise.
Alan
Hi Alan,
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.
-Victoria
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.
I will follow your advise, and create the views as you have outlined.
Alan
THank you I rewarded myself with a cookie!!
Thanks for this helpful information’s,
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.
regards & thanks
saleem
Saleem,
Your users must have some other roles assigned which grant them access to the SmartLists you do not want them to see. I would recommend taking a look at David Musgrave’s Microsoft Dynamics GP Application Level Security Series for detailed resources on GP 10.0 security: http://blogs.msdn.com/developingfordynamicsgp/archive/2008/11/10/microsoft-dynamics-gp-application-level-security-series.aspx.
-Victoria
Hi Victoria,
Correction for steps number 15.
It should be:
15. Open the User Security Setup window.
Tools > Setup > System > User Security
So, when the reader following this number, they will not confuse with step number 16.
Erik
Erik,
Thank you for the correction, I have updated the instructions. I also added ‘Dynamics GP’ to steps 10 and 15 to make it more clear.
-Victoria
Thank you for the information… If we created the smartlist and just wanted to see what accounts it was posted to, what column would I need to add instead of column Purchases Account number?
Thanks,
Mellisa
Mellisa,
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.
-Victoria
Ok great. thank you very much for all your help.. very much appreciated…
Mellisa
Victoria,
First, love the site, its awesome.
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.
Many thanks,
Mitch
Hi Mitch,
Thank you very much!
If any of your users are POWERUSERs they will also have access to SLB, just like ‘sa’. 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 does not 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
-Victoria
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.
Did I miss some steps??
Thanks in advance.
–Erik
Note: I’m using GP10 Sp3.
Erik,
If this is your first SmartList based on a SQL view, or you just switched to GP 10, there is another permission object that you need to give your users:
In Security Tasks:
Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
Operations: View SmartLists with SQL Tables
Also, make sure that you have granted SELECT permissions on the view to DYNGRP.
-Victoria
I understand. Thanks anyways for your quick response. I will probably set up GP 10 in a test environment- separate from our development and test the SP3 theory.
I’ll let you know what I find out.
Thanks
-Dan
Thanks for the info. I have all of those tasks assigned. Perhaps it’s because I’m using SP 1.
Thanks anyways
-Dan
Dan,
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.
-Victoria
Hi,
I have a user who is assigned a security task like you have described, but cannot access the “Calculated Fields” Window.
I logged the script and saw the following:
‘SmartList Builder l_Calculations Button_CHG on form SLB_SmartList_Builder’
‘Get_Security() of form SLB_Security’, 0, 1, “”, 2
‘UserIsPowerUser’, “”, 0
Looks like the Dex is checking to see if the user is a power user.
I have not been able to access Calculated Fields unless the user is a poweruser. Is this what others have seen? Is there a way around this?
Thanks
-Dan
Hi Dan,
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).
-Victoria
Pingback: How to use a SQL view in SmartList Builder - Victoria Yudin
PERFECT!!! Thanks you sooo sooo sooo VERY much.
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.
ANy ideas
Matt,
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
Those are all the ones I found, hope that helps.
-Victoria
Hello Victoria,
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 )
——
Robert
Robert,
Thank you very much for sharing this with us! Very helpful information.
-Victoria
This worked perfectly! Thank you so much for posting this and following up with me. I appreciate it!
Trayc
Trayc,
If this is your first SmartList based on a SQL view, or you just switched to GP 10, you did not need this before, but there is another permission object that you need to give your users:
In Security Tasks:
Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
Operations: View SmartLists with SQL Tables
–Victoria
Trayc,
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.
–Victoria
Victoria-
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?
Thanks
Trayc
Tracey,
You are very welcome! I don’t believe there is a task already in place, we’ve always created a new one or added to an existing one.
-Victoria
Hi Victoria, Thanks for answering my post. This should do the trick. In your experience, is there a task already in place in a default installation? Or have you typically had to create a new task?
Hi Victoria,
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?
Thanks,
Mellisa
Hi Mellisa,
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