A while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.
This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.
The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.
IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity GO CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 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 4.0 International (CC BY-NC-SA 4.0 Int). */ SELECT ['Security User'].UserName AS 'Username' ,['Security User Principal'].Name AS 'Domain Name' ,['Security User'].LastLoginAttempt AS 'Last Login Attempt' ,CASE ['Security User'].RoleType WHEN 2 THEN 'Viewer' WHEN 3 THEN 'Generator' WHEN 4 THEN 'Designer' WHEN 5 THEN 'Administrator' ELSE 'None' END AS 'Role' ,['Control Company'].Code AS 'INTERID' ,['Control Company'].Name AS 'Company Name' FROM ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK) INNER JOIN ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK) ON ['Security User'].UserID = ['Security User Principal'].ID LEFT JOIN ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK) ON ['Security User Principal'].ID = ['Security Company Permission'].PrincipalID LEFT JOIN ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK) ON ['Security Company Permission'].CompanyID = ['Control Company'].ID GO GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP GO