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…

Tags: Analytical Accounting, Transaction Dimension Codes, User Access

Category: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008