web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Access to smart List R...
Microsoft Dynamics GP (Archived)

Access to smart List Reports by a non sa user

(0) ShareShare
ReportReport
Posted on by Mark Lindgren 490

To the Community,

When Logging in as a non sa user, we cannot access the custom Smart List Reports.

I do see on the internet creating a security task and adding smart list to it.

Wasn't there a SQL script needed also?

Thanks for Your help,

MSL

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Manjunath Vemula Profile Picture
    Manjunath Vemula 1,197 on at

    There is not need of SQL script for this, but you have to mark the required tables under SQL Table security setup window which can be accessed from

    GP >> Tools >> Smartlist Builder >> Security >> SQL Table security.

    If a smartlist report is generating data with sa it has to generate for other user as well and vice versa.

    Hope this helps.

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    Mark -

    I have a blog post on the subject of SmartList security that would likely help. Here's a brief overview.

    1. If a SmartList is based on a view in SQL, then make sure you grant Select privileges in SQL to the DYNGRP (all GP users belong to this group).

    2. You will need to add security tasks to a SmartList Object role for all SmartLists built in GP - this doesn't exist in GP out of the box.

    3. Sometimes things get "wonky" and the best approach then is to run the Grant.SQL script on company database.

    4. Make sure any view a SmartList is based on exists in all companies...

    5. Make sure you goto SmartList Builder > Security > SQL Table Security and Grant SmartList security there to all your views.

    redbeardblogging.blogspot.com/.../smartlist-security-setup.html

  • Mark Lindgren Profile Picture
    Mark Lindgren 490 on at

    Manjunath V,

    I do not have Smartlist Builder.  So there is no SmartList Security I can Find.

    Still only works with sa logged in.

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    Mark -

    The SmartList Builder tool is just a wrinkle on top of the root SmartList Security setup.  As a test, try making a test user account a POWERUSER, and see if they can see the SmartList objects.  If they can, then you do have the root security problem.  The blog article covers this too, I was just quickly summarizing the things that cause some consternation with SmartList, so you might not have to read the whole blog.

    Here's the thing to implement if your POWERUSER can access your SmartList Objects.

    Navigate to Microsoft Dynamics GP > Tools > Setup > System > Security Tasks - Create a New Task ID = _SMARTLIST OBJECTS, Task Name = SmartList Object Permissions, Category = System, Product = SmartList, Type = SmartList Object, Series = SmartList Objects - Click the Mark All button; this will provide access to ALL SmartList Objects that currently exist!    Click the Save button to save this new Security Task.

    You will then need to assign this Security Task to a Security Role (a new or existing one) and then assign that Role to the users requiring access.

    If you POWERUSER test account cannot access SmartList Objects, then you should run the Grant.SQL script on your company and system databases, and make sure the database owner is set to DYNSA on all GP related databases.

  • Verified answer
    Mark Lindgren Profile Picture
    Mark Lindgren 490 on at

    I ran the Grant Access script and the Script from 'Smartlist Designer Security' from the Microsoft Dynamics Community. It now works.

  • Mark Lindgren Profile Picture
    Mark Lindgren 490 on at

    Harry,

    Your blog looks good. I already fixed my issue. You can look at my answer.

    Thank you

  • Community Member Profile Picture
    Community Member on at

    Worked like a charm. Thanks.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans