Roles report

Roles are saved in the UserInfo table.

Jan-Pieter

The UserInfo table is actually a view over the tables UserSecurty & UserAccount. UserType = 1 for Roles

select * from dbo.UserSecurity s
inner join dbo.UserAccount a on a.ID = s.ID
where s.UserType = 1The UserInfo view will eventually be deleted, it is there for compatibility reasons.

Best regards,

Tnx!

I dind't search good enough :-)
Feeling less "expert" now. ;-)

-- Role overview (with global rights)
select UI.name rolename,
CASE UI.IsAdministrator WHEN 1 THEN 'Yes' ELSE 'No' END administrator,
CASE CHARINDEX('<DenyAddInRoot>true</DenyAddInRoot>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END rootentries,
CASE UI.CanAdd WHEN 1 THEN 'Yes' ELSE 'No' END globaladd,
CASE UI.CanEdit WHEN 1 THEN 'Yes' ELSE 'No' END globaledit,
CASE UI.CanDelete WHEN 1 THEN 'Yes' ELSE 'No' END globaldelete,
CASE CHARINDEX('<AllowDragAndDrop>false</AllowDragAndDrop>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END dragdrop,
CASE CHARINDEX('<AllowRevealPassword>true</AllowRevealPassword>',UI.CustomSecurity) WHEN 0 THEN 'No' ELSE 'Yes' END revealpassword,
CASE CHARINDEX('<CanExport>false</CanExport>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END export,
CASE CHARINDEX('<CanImport>false</CanImport>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END import,
CASE CHARINDEX('<CanViewDetails>false</CanViewDetails>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END viewdetails,
CASE CHARINDEX('<CanViewGlobalLogs>false</CanViewGlobalLogs>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END viewgloballogs,
CASE CHARINDEX('<CanViewInformations>false</CanViewInformations>',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END viewinformation
from [devolutionsrdm_repository].[dbo].[UserInfo] UI
where UI.UserType = 1
order by UI.name

-- Role overview (with groups and group user rights)
select UI.name rolename, GI.name groupname, 'Yes' viewentries,
CASE PATINDEX('%<DenyAdd>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyAdd>%',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END addentries,
CASE PATINDEX('%<DenyEdit>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyEdit>%',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END editentries,
CASE PATINDEX('%<DenyDelete>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyDelete>%',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END deleteentries
from [devolutionsrdm_repository].[dbo].[GroupInfo] GI, [devolutionsrdm_repository].[dbo].[UserGroupInfo] UGI, [devolutionsrdm_repository].[dbo].[UserInfo] UI
where GI.ID = UGI.GroupInfoID and UGI.UserInfoID = UI.ID and UI.UserType = 1
order by UI.name, GI.name

-- Group overview (with roles and group user rights)
select GI.name groupname, UI.name rolename, UI.fullname accountdescription, 'Yes' viewentries,
CASE PATINDEX('%<DenyAdd>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyAdd>%',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END addentries,
CASE PATINDEX('%<DenyEdit>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyEdit>%',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END editentries,
CASE PATINDEX('%<DenyDelete>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyDelete>%',UI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END deleteentries
from [devolutionsrdm_repository].[dbo].[GroupInfo] GI, [devolutionsrdm_repository].[dbo].[UserGroupInfo] UGI, [devolutionsrdm_repository].[dbo].[UserInfo] UI
where GI.ID = UGI.GroupInfoID and UGI.UserInfoID = UI.ID and UI.UserType = 1
order by GI.name, UI.name

-- User overview (with roles)
select UI.name accountname, RI.name rolename
from [devolutionsrdm_repository].[dbo].[UserInfo] UI, [devolutionsrdm_repository].[dbo].[UserInfo] RI
where UI.usertype = 0 and
UI.CustomSecurity like '%CustomRoles%' and
RI.usertype = 1 and
CAST(UI.customsecurity AS XML).value('data(/CustomSecurity/CustomRoles)[1]','VARCHAR(256)') like '%'+CAST(RI.id AS VARCHAR(200))+'%'
order by UI.name, RI.name

-- Roles overview (with users)
select RI.name rolename, UI.name accountname
from [devolutionsrdm_repository].[dbo].[UserInfo] UI, [devolutionsrdm_repository].[dbo].[UserInfo] RI
where UI.usertype = 0 and
UI.CustomSecurity like '%CustomRoles%' and
RI.usertype = 1 and
CAST(UI.customsecurity AS XML).value('data(/CustomSecurity/CustomRoles)[1]','VARCHAR(256)') like '%'+CAST(RI.id AS VARCHAR(200))+'%'
order by RI.name, UI.name

-- User overview (with roles, groups and group user rights)
select UI.name accountname, RI.name rolename, GI.name groupname, 'Yes' viewentries,
CASE PATINDEX('%<DenyAdd>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyAdd>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END addentries,
CASE PATINDEX('%<DenyEdit>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyEdit>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END editentries,
CASE PATINDEX('%<DenyDelete>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyDelete>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END deleteentries
from [devolutionsrdm_repository].[dbo].[UserInfo] UI, [devolutionsrdm_repository].[dbo].[UserInfo] RI,
[devolutionsrdm_repository].[dbo].[GroupInfo] GI, [devolutionsrdm_repository].[dbo].[UserGroupInfo] UGI
where UI.usertype = 0 and
UI.CustomSecurity like '%CustomRoles%' and
RI.usertype = 1 and
CAST(UI.customsecurity AS XML).value('data(/CustomSecurity/CustomRoles)[1]','VARCHAR(256)') like '%'+CAST(RI.id AS VARCHAR(200))+'%' and
GI.ID = UGI.GroupInfoID and UGI.UserInfoID = RI.id
order by UI.name, RI.name, GI.name

-- Role overview (with groups, group user rights and users)
select RI.name rolename, GI.name groupname, 'Yes' viewentries,
CASE PATINDEX('%<DenyAdd>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyAdd>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END addentries,
CASE PATINDEX('%<DenyEdit>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyEdit>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END editentries,
CASE PATINDEX('%<DenyDelete>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyDelete>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END deleteentries,
UI.name accountname
from [devolutionsrdm_repository].[dbo].[UserInfo] UI, [devolutionsrdm_repository].[dbo].[UserInfo] RI,
[devolutionsrdm_repository].[dbo].[GroupInfo] GI, [devolutionsrdm_repository].[dbo].[UserGroupInfo] UGI
where UI.usertype = 0 and
UI.CustomSecurity like '%CustomRoles%' and
RI.usertype = 1 and
CAST(UI.customsecurity AS XML).value('data(/CustomSecurity/CustomRoles)[1]','VARCHAR(256)') like '%'+CAST(RI.id AS VARCHAR(200))+'%' and
GI.ID = UGI.GroupInfoID and UGI.UserInfoID = RI.id
order by RI.name, GI.name, UI.name

-- Role overview (with users, groups and group user rights)
select RI.name rolename, UI.name accountname, GI.name groupname, 'Yes' viewentries,
CASE PATINDEX('%<DenyAdd>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyAdd>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END addentries,
CASE PATINDEX('%<DenyEdit>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyEdit>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END editentries,
CASE PATINDEX('%<DenyDelete>%'+lower(CAST(GI.id AS varchar(36)))+'%</DenyDelete>%',RI.CustomSecurity) WHEN 0 THEN 'Yes' ELSE 'No' END deleteentries
from [devolutionsrdm_repository].[dbo].[UserInfo] UI, [devolutionsrdm_repository].[dbo].[UserInfo] RI,
[devolutionsrdm_repository].[dbo].[GroupInfo] GI, [devolutionsrdm_repository].[dbo].[UserGroupInfo] UGI
where UI.usertype = 0 and
UI.CustomSecurity like '%CustomRoles%' and
RI.usertype = 1 and
CAST(UI.customsecurity AS XML).value('data(/CustomSecurity/CustomRoles)[1]','VARCHAR(256)') like '%'+CAST(RI.id AS VARCHAR(200))+'%' and
GI.ID = UGI.GroupInfoID and UGI.UserInfoID = RI.id
order by RI.name, GI.name, UI.name

For now I have used the UserInfo view and create some reports on roles (see statements in my previous update).

Maybe you can include these and my earlier statements as reports in a future release of RDM?

Amazing work. Much appreciated. David has add it to our to-do list.

Here is another example of a community member being a Rock Star. Not only do we get a request for a new report but we also get the exact content required and the code to produce the report.

Best regards,