The first of the security SQL views I am posting in this series shows all of the users and the companies to which they have been granted access.
CREATE VIEW [dbo].[uv_AZRCRV_UserAccessAndGranted] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
['User Master'].USERID AS 'User ID'
,['User Master'].USERNAME AS 'Username'
,['User Master'].USRCLASS AS 'User Class'
,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
FROM
SY01400 AS ['User Master']
LEFT JOIN
SY40400 AS ['Class Master']
ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
SY60100 AS ['User-Company Access']
ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN SY01500 AS ['Company Master']
ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
GO
GRANT SELECT ON uv_AZRCRV_UserAccessAndGranted TO DYNGRP
GO