DM5 SQL Queries

Document's SystemID = THING in Security Table
Check what users have access to a document (use docuemtn's SystemID or THING in Docsadm.secuirty table)

select S.THING,S.PERSONORGROUP,S.ACCESSRIGHTS,P.USER_ID,P.FULL_NAME,G.GROUP_ID,P2.USER_ID,P2.FULL_NAME
from DOCSADM.SECURITY S
left join DOCSADM.PEOPLE P on S.PERSONORGROUP = P.SYSTEM_ID
left join DOCSADM.GROUPS G on S.PERSONORGROUP = G.SYSTEM_ID
left join DOCSADM.PEOPLEGROUPS PG on G.SYSTEM_ID = PG.GROUPS_SYSTEM_ID
left join DOCSADM.PEOPLE P2 on PG.PEOPLE_SYSTEM_ID = P2.SYSTEM_ID
where S.THING = 45029530
Check what documents and access associated with a client (use client's client_id, not the system_id)
select p.system_id, p.docnumber, m.system_id, m.matter_id, c.system_id, c.client_id, 
s.accessrights, s.personorgroup from docsadm.profile p 
join docsadm.matter m on p.matter = m.system_id
join docsadm.client c on m.client_id = c.system_id
join docsadm.security s on s.thing = p.system_id
where c.client_id = '2823440'
DM5
DM5 Security Queries
declare @client varchar(12)
declare @matter varchar(12)
declare @groupID varchar(40)
declare @secGroupID varchar(40)
set @client = '0001'            --CHANGE CLIENT NUMBER HERE
set @matter = ''                --CHANGE MATTER NUMBER HERE
set @groupId = 'ZZINT_'+@client+'_'+@matter
 
--Users in group
select p.user_id, p.full_name, g.group_id, g.group_name, g.disabled, g.SYSTEM_ID
from docsadm.people p
join docsadm.peoplegroups pg on pg.people_system_id = p.system_id
join docsadm.groups g on g.system_id = pg.groups_system_id
where g.group_id = @groupID
 
--Security group ID;
select @secGroupID = g.system_id
from docsadm.people p
join docsadm.peoplegroups pg on pg.people_system_id = p.system_id
join docsadm.groups g on g.system_id = pg.groups_system_id
where g.group_id = @groupID
 
--documents being secured
SELECT p.last_edit_date, p.docnumber, p.docname, p1.full_name as Author, s.accessrights as 'AccessRights(2147....=Deny, 255 = grant)',c.client_id, m.matter_id
FROM docsadm.profile p
JOIN docsadm.matter m on p.matter = m.system_id
JOIN docsadm.client c on m.client_id = c.system_id
JOIN docsadm.security s on s.thing = p.system_id
join docsadm.people p1 on p.author = p1.system_Id
WHERE s.personorgroup = @secGroupID
 
--For inclusionary walls, verify that *only* ZZINT groups appear
select distinct g.GROUP_ID, p.USER_ID
from docsadm.security s
left join docsadm.GROUPS g on g.SYSTEM_ID = s.PERSONORGROUP
left join docsadm.PEOPLE p on p.SYSTEM_ID = s.PERSONORGROUP
where thing in
(
SELECT p.system_id
from docsadm.profile p
join docsadm.matter m on m.system_id = p.matter
join docsadm.client c on c.system_id = m.client_id
WHERE c.client_id = @client
and m.matter_id = @matter      --remove this part of where clause if wall is client level (comment out 'and m.matter_id = @matter')
)
 
--select documents and users given access to specific matter (in this example matter 2001)
declare @Matter varchar (12)
set @Matter = '2001'
select M.MATTER_ID, M.MATTER_NAME, P.DOCNAME, case ACCESSRIGHTS when '255' then 'yes'else 'no' end "accessrights", S.PERSONORGROUP, G.GROUP_NAME, G.GROUP_ID, PE.USER_ID from DOCSADM.MATTER M
join DOCSADM.PROFILE P on M.MATTER_ID = P.MATTER
join DOCSADM.SECURITY S on P.SYSTEM_ID = S.THING
join DOCSADM.GROUPS G on S.PERSONORGROUP = G.SYSTEM_ID
join DOCSADM.PEOPLEGROUPS PG on G.SYSTEM_ID = PG.GROUPS_SYSTEM_ID
join DOCSADM.PEOPLE PE on PG.PEOPLE_SYSTEM_ID = PE.SYSTEM_ID
where m.MATTER_ID = @Matter