Day: July 13, 2020

Accounts accessible to user when Account Level Security in use

Ian Grieve5 minute read0 comment

Microsoft Dynamics GPthe scrpt below returns all the accounts assigned to a specific user (defined at the top) using the Account Level Security module of Microsoft Dynamics GP.

The script is configured to only return values if the Account Level Security module is enabled via the Company Setup window (Administration area page » Setup » Company » Company). To remove this restriction the where clause with the comment following it should be removed.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @UserID AS VARCHAR(100) = 'iang'

SELCT
	['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	DYNAMICS..SY01400 AS ['User Master'] -- Users Master (SY01400)
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME[/sqlpink()
LEFT JOIN
	(
		SELCT
			RELID
			,ACTINDX
		FROM
			GL00100F1 -- Account Master Filter1 (GL00100F1)
		UNION ALL
			SELCT
				RELID
				,ACTINDX
			FROM
				GL00100F2 -- Account Master Filter2 (GL00100F2)
			UNION ALL
				SELCT
					RELID
					,ACTINDX
				FROM
					GL00100F3 -- Account Master Filter3 (GL00100F3)
				UNION ALL
					SELCT
						RELID
						,ACTINDX
					FROM
						GL00100F4 -- Account Master Filter4 (GL00100F4)
	) AS ['Account Level Security Accounts']
		ON
			['Account Level Security Accounts'].RELID = ['User Master'].RELID
LEFT JOIN
	GL00105 AS ['Account Index Master'] -- Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Account Level Security Accounts'].ACTINDX
LEFT JOIN
	GL00100 AS ['Account Master'] -- Breakdown Account Master (GL00100)
		ON
			['Account Master'].ACTINDX = ['Account Level Security Accounts'].ACTINDX
WHERE
	['Company Master'].SECOPTS = 0x01000000 -- ALS enabled in Company Setup
AND
	['User Master'].USERID = @UserID

If you wanted to use the query in a SmartList or other report, the User ID parameter could be removed and the User ID column added into the list of returned accounts.