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 (https://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