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…