SQL Script to provide access to Transaction Dimension Codes to Users

Hi all

I had posted an article sometime back titled Macro to provide access to Transaction Dimension Codes to Users where I had provided a SQL script to create a macro code to provide access to all users to all transaction dimension codes setup in the company.

I had received a few comments from user community if the same task which is done by macro can be done using a SQL script. So I decided to write up a simple script to provide access to all users to all transaction dimensions and dimension codes setup in a company.

DECLARE @INTERID CHAR(5)
DECLARE @CMPANYID INT
DECLARE @CMPNYNAM CHAR(30)
 
DECLARE crCompanies CURSOR
    FOR SELECT  INTERID
        FROM    DYNAMICS..SY01500
        ORDER BY INTERID
 
OPEN crCompanies
 
FETCH NEXT FROM crCompanies INTO @INTERID
 
WHILE @@fetch_status = 0
    BEGIN
        PRINT '/* Updating AA Code Access in the database ' + RTRIM(@INTERID)
            + ' */'
        PRINT 'DELETE FROM ' + RTRIM(@INTERID) + '..AAG02000 '
        PRINT 'INSERT INTO ' + RTRIM(@INTERID)
            + '.dbo.AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust)
	(SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM '
            + RTRIM(@INTERID)
            + '..AAG00401 A CROSS JOIN DYNAMICS..SY01400 B) '
 
        FETCH NEXT FROM crCompanies INTO @INTERID
    END
 
CLOSE crCompanies
 
DEALLOCATE crCompanies

Note that this script will delete any access records in the table and re-define access to all the users in that company to all the transaction dimension and codes.

This script is free for use and does not cover any warranties. As always, please have a backup of your DYNAMICS and company databases before you execute this script. Also make sure all users have logged out of GP to ensure no data discrepancy occurs. :)

Hope this script helps you all… Until next post…

VN:F [1.9.13_1145]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

May 11, 2010 · veeyeskay · 10 Comments
Tags: , ,  Â· Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 683

  • Anonymous

    Hi, I am looking to automatically grant all users access to old & new cards regarding AA … does this work for Great Plains 9 or just 2010?  Thanks in advance. 

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
    • Sivakumar Venkataraman

      Hi, the script has been tested on GP10 and GP2010. However, it has not been tested on v9. You can test this code on a test environment and if it works fine, you can use in on the live environment. Hope this post helped.

      VA:F [1.9.13_1145]
      Rating: 0.0/5 (0 votes cast)
      VA:F [1.9.13_1145]
      Rating: 0 (from 0 votes)
      • Anonymous

        Hello. I ran it in our test environment and it did not effect any permissions like it should have…. the query did not error out though in SENSE, so i thought it would have been fine. One one of our company databases it works just fine from a script that was ran years ago… I just can’t seem to find the code that was used because gp9 is so outdated!!! :(

        VA:F [1.9.13_1145]
        Rating: 0.0/5 (0 votes cast)
        VA:F [1.9.13_1145]
        Rating: 0 (from 0 votes)
        • Sivakumar Venkataraman

          Once the query ran, did you see “xxx rows updated” or something like that? Ideally this query inserts records into the AAG02000 (user AA code access table). So if records were inserted, then it means that the permissions should have been granted.

          VA:F [1.9.13_1145]
          Rating: 0.0/5 (0 votes cast)
          VA:F [1.9.13_1145]
          Rating: 0 (from 0 votes)
          • Anonymous

            Thanks for your reply.  Here is the output from the query I ran (it appears to have worked):

            /* Updating AA Code Access in the database AZDF */ 
            DELETE FROM AZDF..AAG02000 
            INSERT INTO AZDF.dbo.AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) 
            (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM AZDF..AAG00401 A CROSS JOIN DYNAMICS..SY01400 B) 
            /* Updating AA Code Access in the database AZPB */ 
            DELETE FROM AZPB..AAG02000 
            INSERT INTO AZPB.dbo.AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) 
            (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM AZPB..AAG00401 A CROSS JOIN DYNAMICS..SY01400 B) 
            /* Updating AA Code Access in the database TWO */ 
            DELETE FROM TWO..AAG02000 
            INSERT INTO TWO.dbo.AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) 
            (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM TWO..AAG00401 A CROSS JOIN DYNAMICS..SY01400 B)

            Surprisingly it didn’t work on old cards, and doesn’t touch new ones either.  The “Distribute” and “Adjust” columns are still empty … we want all users to have all access upon creating brand new Transaction Dimension Codes (and also update all old ones too).  

            VA:F [1.9.13_1145]
            Rating: 0.0/5 (0 votes cast)
            VA:F [1.9.13_1145]
            Rating: 0 (from 0 votes)
          • Sivakumar Venkataraman

            Can we do a remote session so that I can take a look into this issue? Please email me at veeyeskayATgmailDOTcom so that I can setup a session and send you the link.

            VA:F [1.9.13_1145]
            Rating: 0.0/5 (0 votes cast)
            VA:F [1.9.13_1145]
            Rating: 0 (from 0 votes)
  • Santosh

    Great work….thanks fr sharing

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)

  • Pingback: Automatic User Access for new AA Codes - About Dynamics, Development and Life

  • veeyeskay

    You are welcome. :)

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
  • PR

    Many Thanks. Great Stuff !

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)