You’re offline. This is a read only version of the page.
Hi Team,
we are using NAV 2018 source. I am SQL developer, where I want all users information related to Roles, Groups, Access levels for auditing purposes. I have the following tables. But the result I am getting it's huge. Around 10 Million records popping up which I feel might not be correct. Below are the table list and joining condition. Kindly let and let me know if I am doing it right.
Share
Reportselect DISTINCT
u.[UserName]as UserName
,u.WindowsSecurityID as UserSID
,u.[FullName]AS UserFullName
,u.[ContactEmail]as UserEmail
,isnull(ugm.[UserGroupCode],'') UserGroupCode
,isnull(ug.[Name],'') UserGroupCodeDescription
,isnull(ug.[DefaultProfileID],'') UserGroupProfile
,ug.[AssigntoAllNewUsers] UserGroupAssigntoAllNewUsers
,ac.[RoleID]as AccessRole
,ps.[Name] RoleDescription
, o.[Name] as TableName
, o.[ID] as TableID
,u.[State]as UserStatus
,ac.CompanyName
,CASE WHEN p.[ReadPermission] = 1 THEN CONVERT(NVARCHAR(20),'Read Permission') END AS Read_Permission
,CASE WHEN p.[InsertPermission]=1 THEN CONVERT(NVARCHAR(20),'Insert Permission') END AS Insert_Permission
,CASE WHEN p.[ModifyPermission]=1 THEN CONVERT(NVARCHAR(20),'Modify Permission') END AS Modify_Permission
,CASE WHEN p.[DeletePermission]=1 THEN CONVERT(NVARCHAR(20),'Delete Permission') END AS Delete_Permission
,CASE WHEN p.[ExecutePermission]=1 THEN CONVERT(NVARCHAR(20),'Execute Permission') END AS Execute_Permission
from
[User] u (nolock)
left join
AccessControl ac (nolock)
on ac.[UserSecurityID]=u.[UserSecurityID]
left join
Permission p (nolock)
on ac.[RoleID]=p.[RoleID]
and p.[ObjectType]=0 -- (Type = 0 is for Table .. I guess)
and (p.[ReadPermission] = 1 or p.[InsertPermission] = 1 or p.[ModifyPermission] = 1 or p.[DeletePermission] = 1 or p.[ExecutePermission] = 1 )
left join
Object o (nolock)
ON o.[ID] = p.[ObjectID] and [Type]=0 -- (Type = 0 is for Table .. I guess)
left join
PermissionSet ps
ON ps.RoleID = p.RoleID
left join
UserGroupMember ugm (nolock)
ON ugm.UserSecurityID = ac.UserSecurityID and ugm.CompanyName = ac.CompanyName
left join
UserGroup ug (nolock)
ON ug.Code = ugm.UserGroupCode
where
NULLIF(u.[UserName],'') IS NOT NULL 
Hello,
You can try with. Hope it will help you.
select y.[User Name],x.[Role ID],Name 'Permission Name', case y.State when 0 then 'Enabled' when 1 then 'Disabled' end as 'User Status',
[UAT_TEST$User Setup].[Unit Filter],[UAT_TEST$User Setup].[No_ of Back Days],
[Posting Allowed]
from [Access Control] x
left join [User] y on x.[User Security ID]=y.[User Security ID]
left join [UAT_TEST$User Setup] on y.[User Name]=[UAT_TEST$User Setup].[User ID]
left join [Permission Set] z on x.[Role ID]=z.[Role ID]
where y.[User Name]<>''
order by y.[User Name] asc OFFSET 0 ROWS
GO
All responses (
Answers (
387
