We'd like to create a SQL query that displays the permissions for each user. I am having a problem finding the SQL table that stores the permissions for a user. I'm sure it's tied to the assigned role. Regardless, I cannot find the table that stores the information.
For example, I would like to be able to display the permissions in this window for this user. We're trying to get all users' permissions.
Interesting question ... I don't have any clients who have set any security deeper than the pre-defined roles.
When we need to report on those roles, I use the following script (on the ManagementReporter database):
--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 reporting.securityuser a (nolock)
JOIN reporting.securityprincipal b (nolock)
ON a.userid = B.id
LEFT JOIN reporting.securitygroupuser c (nolock)
ON a.userid = c.userid
LEFT JOIN reporting.securityprincipal d (nolock)
ON c.groupid = d.id
ORDER BY d.[description],
a.roletype
I see other tables with names that begin with reporting.security that may have the information you are looking for. (Since I don't have any data at that level, I'm not certain what you will find as you review the tables.)
Note: The current table structure that includes a period in the name was a change from early versions of MR. If you are on a very old version, you may see different table naming structure but while the exact names changed, I recall the naming logic followed the same pattern.
Was this reply helpful?YesNo
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
167
Share
Report
All responses (
Answers (
