Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

SQL Script for Account Level Security

SQL Script for Account Level Security

Rate This
  • Comments 5

Sivakumar Venkataraman - Click for blog homepageI recently joined Microsoft as a Partner Consultant for the Global Partner Services team. I have been associated for more than 8 years in the design, development and implementation of Microsoft Dynamics GP in India, Middle East and US. This is my first post in this blog and it feels great to be posting alongside community experts like David Musgrave, Patrick Roth and others.

Microsoft Dynamics GP offers the Account Level Security option to restrict the access of GL accounts to specific users. There is no specific report (or) Smartlist which is available for viewing the details of the accounts which each user has access to.

The script below gives us the details of the various users in the system and the accounts they have access to. If a user does not have access to any accounts, the user will not be listed in the results.

Code Example
/******************************************************************
Created Jun 23, 2012 
This script is used for generate the details of users and the 
GL accounts which they have access to, when account level security
is activated in Dynamics GP.

Tables Used:
GL00100F1 - Account Master Filter1
GL00100F2 - Account Master Filter2
GL00100F3 - Account Master Filter3
GL00100F4 - Account Master Filter4
GL00105 - Account Index Master
SY01400 - Users Master
 
Revision History
No.		User		Date			Description
*******************************************************************/
SELECT  C.USERID ,
        LTRIM(RTRIM(B.ACTNUMST)) AS ACCOUNTNO
FROM    ( SELECT    ACTINDX ,
                    RELID
          FROM      dbo.GL00100F1
          UNION ALL
          SELECT    ACTINDX ,
                    RELID
          FROM      dbo.GL00100F2
          UNION ALL
          SELECT    ACTINDX ,
                    RELID
          FROM      dbo.GL00100F3
          UNION ALL
          SELECT    ACTINDX ,
                    RELID
          FROM      dbo.GL00100F4
        ) A
        INNER JOIN GL00105 B ON A.ACTINDX = B.ACTINDX
        LEFT OUTER JOIN DYNAMICS..SY01400 C ON A.RELID = C.RELID
WHERE   C.SECACCS = 0x00000000
UNION ALL
SELECT  B.USERID ,
        LTRIM(RTRIM(A.ACTNUMST)) AS ACCOUNTNO
FROM    GL00105 A
        CROSS JOIN DYNAMICS..SY01400 B
WHERE   B.SECACCS = 0x01000000
 

The script is also available as an attachment at the bottom of this post.

We could also create a view based on this script and then create custom Smartlists using Smartlist Builder.

Hope this helps the community...

Until next post!

Siva

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Attachment: MBS_AccountLevelSecurity.zip
Page 1 of 1 (5 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post