CREATE VIEW [dbo].[uv_AZRCRV_UserAccessAndGranted] AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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
Back in November I posted a SQL View which could be used in SmartList Designer to return the users, company and role assignments. This view is now accompanied by several others which allow for reporting on the Dynamics GP security setup at several different levels.
These have been done as SQL views so they can easily be plugged into SmartList Designer which does not have an import/export function, but they can also be used in SmartList Builder, should the client be licensed for this add-on, or any other reporting tool.