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

Community site session details

Session Id :
Microsoft Dynamics GP (Archived)

Smartlist Designer Security

(1) ShareShare
ReportReport
Posted on by 5,985

I am trying to make a simple smartlist using Smartlist Designer but am having trouble figuring out how security is managed. Using my power user I can see the new smartlist but no data is populated, when logged in as 'sa' the list populates fine. The tables this smartlist needs to access are IV10000 and SY03900.

Can anyone point me in the right direction?

Thank you,

Kirk Livermont

*This post is locked for comments

I have the same question (0)
  • Mahmoud Saadi Profile Picture
    32,738 on at
    RE: Smartlist Designer Security

    Check this out,Smart List Designer

    Hope this helps,

  • KirkLivermont Profile Picture
    5,985 on at
    RE: Smartlist Designer Security

    Hi Mahmoud,

    Thank you for your response.

    Does Smartlist Designer create a new SQL view? I thought this wouldn't apply because I am referencing GP tables not a custom view.

    Regards,

    Kirk Livermont

  • Mahmoud Saadi Profile Picture
    32,738 on at
    RE: Smartlist Designer Security

    Absolutely not, the smart list designer doesn't create a new view. The SQL script is dynamically built up each time you press on the new Smart List, key data fields are stored in ADH tables (ADH00100, ADH00101, ADH00102, ADH00103, ADH00104).

    This is actually the first time I get an inquiry on the Smart List designer security issue, I have been trying to figure out several test scenario on my test machine. I am trying to figure out why users other than "sa" are not being able to see the date, although, they are members of the DYNGRP.

    I can tell that this is security issue on the SQL level, since the test user I created can see the actual data within the new smart list (created with Smart List Designer) when granted dbadmin role.

  • Verified answer
    Naga Kiran Profile Picture
    on at
    RE: Smartlist Designer Security

    When a non - power user provided with access to Smartlist Designer, the user-defined smartlists will not showup  in the Smartlist tree view as soon as the non-power user saves it in SmartList Designer.

    A power user needs to give access to that new smartlist object in Security Task setup window to the Secutiry Task the non-power user assigned with.

    Hope this helps.

  • Verified answer
    KirkLivermont Profile Picture
    5,985 on at
    RE: Smartlist Designer Security

    This turned out to be a bug with the smartlist calling a function that the DYNGRP wasn't granted execute permissions on. With the help of Microsoft support we were able to get this resolved and the bug is slated to be fixed in a future release.

  • SteveSieber Profile Picture
    35 on at
    RE: Smartlist Designer Security

    Here is the offending function that causes the new smartlist designer objects to return no data.

    /*  Date: 01/09/2014  Time: 8:28:24

    SQLSTATE:(42000) Native Err:(229) stmt(122818304):*/

    [Microsoft][SQL Server Native Client 10.0][SQL Server]The EXECUTE permission was denied on the object 'DYN_FUNC_Boolean_All', database 'BROWN', schema 'dbo'.*/

    Just grant Execute access to DYNGRP for this function.

  • Community Member Profile Picture
    on at
    RE: Smartlist Designer Security

    I had the same problem - I created a SmartList with designer, but can only get results logged in as SA.  I granted the above execute access to the DYNGRP, but still don't get any results as anyone but SA. :(

    Also,  I tried making my newly-created smartlist available to another security role, but users assigned that role still can't see the new smartlist.

    If anyone can help with either of the above it would be greatly appreciated.

  • Verified answer
    SteveSieber Profile Picture
    35 on at
    RE: Smartlist Designer Security

    All of the smartlist designer objects call one or more DYN functions, you need to grant DYN functions to DYNGRP, use the script below to accomplish this.

    GRANT EXECUTE ON dbo.DYN_FUNC_Salary_Change TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Series_GL_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Series_Tax_Detail_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Sick_Time_Accrual_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_SOP_Distribution_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_1099_Box_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_SOP_HDR_Flags TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_1099_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_SOP_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_ABC_Code TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_SOP_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Account_Category_Number TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Source_Indicator TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Account_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Statement_Cycle TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Aging_Bucket TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Status_Group TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Allocate_By TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Summary_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Attendance_TRX_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Tax_Detail_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Balance_For_Calculation TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Tax_Options TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Balance_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Tax_Schedule_Source TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Boolean_All TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Tax_Schedule_Source_from_Sales_Line_Item TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Cash_Receipt_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Time_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Closed_Year TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Tip_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_CM_Trx_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Transaction_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Commission_Applied_To TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Transfer_From_QTY_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Computer_TRX_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Transfer_To_QTY_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Control_Blanket_By TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_TRX_Frequency TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Control_Type_CM TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Type_of_Employment TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Control_Type_PM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Typical_Balance TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Conversion_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Vacation_Accrual_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Cost_Calculation_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Valuation_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Cost_Selection TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Vendor_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Credit_Limit TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Voided TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Credit_Limit_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Void_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Currency_Decimals TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Which_Cash_Account_For_Pay TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Decimal_Places TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Workflow_Approval_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Decimal_Places_Currency TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Workflow_Priority TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Decimal_Places_QTYS TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Writeoff TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Default_Cash_Account_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Deposit_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Display_In_Lookups TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Prefix_RM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_CM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_GL_Sum TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_GL_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_IV_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_PM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_POP_Line_Items TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_Purch_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_RM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_Sls_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Status_SOP_Line_Items TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Type_IV_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Type_PM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Type_RM_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Document_Type_Tax_Detail_Trx TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_DTA_Series TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_EIC_Filing_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Ethnic_Origin TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Federal_Filing_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Finance_Charge_Amt_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Fixed_Or_Variable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Forecast_Consumption_Period TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Free_On_Board TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Freight_Taxable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Functional_Currency_Decimals TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Gender TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_GL_Header_Valid TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_GL_Ledger_Description TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_GL_Ledger_Name TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_GL_Line_Valid TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_History_Year TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_HR_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Item_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Item_Tracking_Option TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_IV_Item_Taxable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Kit_COGS_Account_Source TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Line_Origin TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Line_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_MaritalStatus TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Markdown_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Maximum_Invoice_Amount_For_Vendors TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Maximum_Writeoff_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_MC_Transaction_State TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Minimum_Payment_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Misc_Taxable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Open_Year TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Order_Fulfillment_Shortage_Default TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Order_Policy TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Original_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Originating_DTA_Series TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Originating_TRX_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Originating_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Payment_Entry_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Payroll_Record_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_POP_Order_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_POP_Tax_Schedule_Source TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_POP_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_POP_Vendor_Selection TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Posting_Status_MDA TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Posting_Status_POP_Receipts TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Posting_Status_POP_Receipt_Lines TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Posting_Status_SOP_Line_Items TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Posting_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_Inventory_In TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_Payroll_In TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_Purchasing_In TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_Results_To_Customer TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_Results_To_Vendor TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_Sales_In TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Post_To_Cash_Account_From TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_PO_Line_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_PO_Line_Status_Orig TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_PO_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_PO_Status_Orig TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_PO_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Price_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Printing_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Priority TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Purchase_Freight_Taxable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Purchase_IV_Item_Taxable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Purchase_Misc_Taxable TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Purchase_Receipt_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Purchase_Tax_Options TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Purchasing_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_QTY_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Rate_Calculation_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Rate_Calc_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Rate_Calc_Method_POP_Line_Items TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Rate_Calc_Method_POP_Receipts TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Receipt_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Receiving_MC_Transaction_State TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Receiving_Rate_Calc_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Record_Status TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Record_Type TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Replenishment_Level TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Replenishment_Method TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Reversing_Closed_Year TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_Reversing_Year TO DYNGRP

    GRANT EXECUTE ON dbo.DYN_FUNC_RM_Document_TypeAll TO DYNGRP

  • Community Member Profile Picture
    on at
    RE: Smartlist Designer Security

    That did it.  Thanks so much.

  • SteveSieber Profile Picture
    35 on at
    RE: Smartlist Designer Security

    Awesome, glad to help out.

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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans