I recently had a client, who is publicly traded, who needed a report on the security configured within Management Reporter. To that end, I developed these SQL scripts which provided them with the information to satisfy their auditors. Hopefully these will prove useful to someone else, which is why I am posting them here!
--MR Security Script #1
--MR UserNames, along with DomainNames, MR Role assignment and MR Group assignment
select a.UserName, b.[Name] as DomainName,
case a.RoleType
when 5 then 'Admin'
when 4 then 'Designer'
when 3 then 'Generator'
end RoleDescr,
d.[Name] as GroupID, d.[Description] as GroupName
from SecurityUser a (nolock)
join SecurityPrincipal b (nolock)
on a.UserID = B.ID
left join SecurityGroupUser c (nolock)
on a.UserID = c.UserID
left join SecurityPrincipal d (nolock)
on c.GroupID = d.ID
order by d.[Description], a.RoleType
--MR Security Script #2
--Company Access as assigned by Group
select c.[Name] as GroupID, c.[Description] as GroupName,
b.Code as CompanyID, b.[Name] as CompanyName
from SecurityCompanyPermission a (nolock)
join ControlCompany b (nolock)
on a.CompanyID = b.[ID]
left join SecurityPrincipal c (nolock)
on a.PrincipalID = c.[ID]
order by c.[Name], b.Code
*This post is locked for comments
I have the same question (0)
