I need a SQL query for CA
Hi,
I need help creating a sql query to collect the following data:
* User Name/Login ID
* First Name
* Last Name
* Email address
* Account Disabled Flag
* Entitlement within the application once logged in (i.e., role, profile, permissions)
Hi run following SQL query to find out Roles and permissions :
SELECT DISTINCT(tcp.PermissionID)
, tp.Name AS 'Permission Name'
, tr.RoleName AS 'Role'
, tcp.CaseID
, tc.Name AS 'Case/Dept/Folder Name'
, CASE WHEN (tc.FolderType = 330 AND tc.Type = 101) THEN 'Case'
WHEN (tc.FolderType = 330 AND tc.Type = 102) THEN 'Department'
WHEN (tc.FolderType = 331 AND tc.Type = 101) THEN 'Folder'
WHEN (tc.FolderType = 332 AND tc.Type = 101) THEN 'Hidden Folder'
ELSE 'Other' END 'FolderType'
, tpl.PrincipalID
, tpl.PrincipalName
, tpl.PrincipalLogin
, tcp.StartDate
, tcp.EndDate
FROM tblHistCasePermission AS tcp
INNER JOIN tblPermission AS tp ON tcp.PermissionID = tp.PermissionID
INNER JOIN tblIntRolePermission tirp ON tcp.PermissionID = tirp.PermissionID
INNER JOIN tblRole tr ON tirp.RoleID = tr.RoleID
INNER JOIN tblCase AS tc ON tc.CaseID = tcp.CaseID
INNER JOIN tblPrincipal AS tpl ON tpl.PrincipalID = tcp.PrincipalID
ORDER BY tpl.PrincipalName, tc.Name, tr.RoleName, tp.Name, tcp.StartDate, tcp.EndDate