October 8, 2008 
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.
- Open the Security Task Setup window.
Dynamics GP > Tools > Setup > System > Security Tasks - Open an existing task or create a new task.
- In the Product list, choose SmartList.
- In the Type list, choose SmartList Object.
- In the Series list, choose SmartList Objects.
- 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.
- Click Save.
- 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.
- Open the Security Role Setup window.
Dynamics GP > Tools > Setup > System > Security Roles - Open an existing role or create a new role.
- Find the task you create is Step 3 and select the check box next to it.
- Click Save.
- 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.
- Open the User Security Setup window.
Dynamics GP > Tools > Setup > System > User Security - Select the user and company.
- Find the role you created in Step 11 and select the check box next to it.
- Click Save.
- Reward yourself with a cookie. 🙂
~~~~~
Update on Dec 9, 2008: If 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 GP version/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.
Update on Feb 10, 2011: Updated title and content to reflect that this works for GP 2010 as well.
Update on Aug 19, 2014: Updated content to reflect that this works for GP 2013.
Victoria Yudin 
Excellent, what a webpage it is! This web site provides useful data to us, keep it up.
LikeLike
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.
LikeLike
Awesome! This is exactly what I was looking for. Been struggling getting users to see reports I have created. Thank you!
LikeLike
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.
-D
LikeLike
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?
LikeLike
Brent,
With weird security issues like this, you might want to install the Support Debugging Tool and see if it can help you narrow down the issue: http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/12/16/solving-security-privileges-errors-using-the-support-debugging-tool.aspx
-Victoria
LikeLike
Hi Victoria,
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.
Thanks,
Donald
LikeLike
Hi Donald,
I think this will happen if this is the first SmartList you have created using a SQL table or view. Please take a look at the section in this blog post that starts with ‘update on Dec 9, 2008’.
-Victoria
LikeLike
Thank you so very much Victoria but this is not the first SL I have created for this client. Is there anything that we might have overlooked or is this a possible bug with certain versions of GP?
Thanks Donald
LikeLike
Donald,
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.
-Victoria
LikeLike
That worked!!! Odd though as I ran the grant.sql script earlier. Thanks again Victoria 🙂
LikeLiked by 1 person
hi Victoria,
i have smartlists that restricted to one user. now, i want to make those smartlists to global. is there a way to un-restrict all at once? or at least change them to another user.
thanks,
Aaron Nguyen
LikeLike
Aaron,
What version of GP are you using?
-Victoria
LikeLike
hi Victoria,
we’re using GP 10.00.1579
LikeLike
and we also don’t have smartlist builder installed 😦
LikeLike
Aaron,
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.)
-Victoria
LikeLike
hi Victoria,
the smartlist is not created by smartlist builder. It was based on userID, so only that specific userID has access to it.
and i am trying to either transfer that to another user or make a copy of it.
I found something in
select * from dynamics..ASIEXP81 where USERID = xxx. I made a copy of 1 smartlist favorite and assigned to me. however, i can’t find that anywhere in GP
LikeLike
Aaron,
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:
-Victoria
LikeLike
Victoria,
what is level = 1, 4? i am just curious to know why do we have to change that since i don’t want any other users have access to that smartlist except that specific person.
LikeLike
Aaron,
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.
-Victoria
LikeLike
hi Victoria,
Basically, i have smartlist #1 and that only show up on user A. I need to somehow have user B have access to that smartlist #1.
Hope this will make sense to you 🙂
thanks,
Aaron
LikeLike
Aaron,
There are SmartList objects and SmartList favorites – which of those are you talking about?
-Victoria
LikeLike
hi Victoria,
I guess both, SmartList objects and SmartList favorites.
LikeLike
Aaron,
For SmartList objects you can specify what users have access to them using GP security. For SmartList Favorites you have 4 options:
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.
-Victoria
LikeLike
hi Victoria,
How can i change/transfer that Smartlist favorites to other userID? i could not find that in GP elsewhere.
thanks,
Aaron
LikeLike
Aaron,
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.
-Victoria
LikeLike
Thanks Visctoria, was very useful.
LikeLike
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?
LikeLike
Pamela,
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.
-Victoria
LikeLike
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.
LikeLike
You get a cookie from me. Thanks! Different from v 9.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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.
Click to access smartlist%20builder%20-%20cross%20database%20queries.pdf
LikeLike
THank you I rewarded myself with a cookie!!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Ok great. thank you very much for all your help.. very much appreciated…
Mellisa
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
Thanks for the info. I have all of those tasks assigned. Perhaps it’s because I’m using SP 1.
Thanks anyways
-Dan
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
PERFECT!!! Thanks you sooo sooo sooo VERY much.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Robert,
Thank you very much for sharing this with us! Very helpful information.
-Victoria
LikeLike
This worked perfectly! Thank you so much for posting this and following up with me. I appreciate it!
Trayc
LikeLike
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
LikeLike
This solution also works for giving users access to Smartlist Builder created Smartlists that have used Data Connections.
Georgina
p.s. Thanks for posts they have been most useful
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Kevin,
They are stored in the SQL tables. In the system database (DYNAMICS).
-Victoria
LikeLike