Reference ZenDesk: 1929435

 

Description:  We received a client request to generate a report with the below parameters for the client

* Firstname

* Lastname

* Employee ID

* User Rights

* Date Access was granted

* Last login date

* Active Status

 

Solution:

Run the below script on SQLPVReloAccess

 

select c.cont_uid, c.cont_fname as [FirstName]

, C.cont_lname as [LastName]

,ru.roles

--,sso.Id

,sso.SSOIdentity as [EmployeeID]

--,df.filterType

--,df.filterName

--,df.filterValue

,O.org_name as RestrictDivisionName

,C.cont_status

from contact c

left join GRS_OE_RBS_User ru on c.cont_uid = ru.cont_uid

left join grs_oev_SSOIdentity sso on c.cont_uid = sso.id

left join GRS_OE_DataSecurity_Filters df on c.cont_uid = df.cont_uid

left join organization O on df.filterValue = O.org_uid and df.filterName = 'Division'

where c.cont_org_uid in (select org_uid from organization where org_root_uid = 189125)

 

Highlight all fields and headers and paste into an Excel file for the requestor

 

BGRS Users who can access the client

select distinct * from serviceengine.dbo.contact cc with (readuncommitted)
inner join contact_team ct with (readuncommitted) on ct.cont_uid = cc.cont_uid
where
ct.cont_team in ('c340')   --replace C340 with code for team that client is assigned to .  Values in Code table
and cc.cont_status = 'A'