keys

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 in GP 10.0 and GP 2010. Because this is so different from prior 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.

  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.
    • 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
  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.

 ~~~~~

Update on Feb 10, 2011: Updated title and content to reflect that this works for GP 2010 as well.

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

  1. 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

    Like this

    • Aaron,

      What version of GP are you using?

      -Victoria

      Like this

      • hi Victoria,

        we’re using GP 10.00.1579

        Like this

        • and we also don’t have smartlist builder installed :(

          Like this

          • 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

            Like this

            • 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

              Like this

              • 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:

                update ASIEXP81
                set ASI_Favorite_Save_Level = 1, 
                USERID = ''
                where ASI_Favorite_Save_Level = 4
                

                -Victoria

                Like this

                • 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.

                  Like this

                  • 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

                    Like this

                    • 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

                      Like this

                    • Aaron,

                      There are SmartList objects and SmartList favorites – which of those are you talking about?

                      -Victoria

                      Like this

                    • hi Victoria,

                      I guess both, SmartList objects and SmartList favorites.

                      Like this

                    • Aaron,

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

                      1. User ID – the user that created the SmartList (in all companies)
                      2. User Class – all users in the same class as the user who created it
                      3. Company – all users in the company you’re logged into when the Favorite is created
                      4. 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.

                      -Victoria

                      Like this

                    • hi Victoria,

                      How can i change/transfer that Smartlist favorites to other userID? i could not find that in GP elsewhere.

                      thanks,

                      Aaron

                      Like this

                    • 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

                      Like this

  2. Thanks Visctoria, was very useful.

    Like this

  3. 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?

    Like this

  4. 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.

    Like this

  5. You get a cookie from me. Thanks! Different from v 9.

    Like this

  6. 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

    Like this

  7. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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.

          Like this

          • 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

            Like this

  8. THank you I rewarded myself with a cookie!!

    Like this

  9. 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

    Like this

  10. 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

    Like this

  11. 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

    Like this

    • 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

      Like this

  12. 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

    Like this

    • 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

      Like this

  13. 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.

    Like this

    • 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

      Like this

  14. 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

    Like this

  15. Thanks for the info. I have all of those tasks assigned. Perhaps it’s because I’m using SP 1.

    Thanks anyways
    -Dan

    Like this

    • 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

      Like this

  16. 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

    Like this

    • 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

      Like this

  17. PERFECT!!! Thanks you sooo sooo sooo VERY much.

    Like this

  18. 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

    Like this

    • 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

      Like this

  19. 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

    Like this

  20. This worked perfectly! Thank you so much for posting this and following up with me. I appreciate it!

    Trayc

    Like this

  21. 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

    Like this

    • 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

      Like this

  22. 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

    Like this

  23. 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

    Like this

  24. 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

    Like this

  25. 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?

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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.

          Like this

Trackbacks/Pingbacks

  1. Tips and tricks for setting up User Security in Microsoft Dynamics GP | Dynamics GP Help - August 9, 2011

    [...] Granting access to a new SmartList in Dynamics GP [...]

    Like this

  2. About Dynamics, Development and Life - October 1, 2010

    Security Task/Role associated with a Dynamics GP Window…

    One of the most common GP queries is “How to give access to a GP Window” ? David Musgrave from Microsoft…

    Like this

  3. How to use a SQL view in SmartList Builder - Victoria Yudin - April 20, 2009

    [...] This blog – Granting access to a new SmartList in GP 10.0 [...]

    Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,360 other followers

%d bloggers like this: