AX 2009 Users

(1) Share

Report

Posted on by Community Member

Hello,

Is there a way that I can generate the list of users in AX 2009 and which user group they belong, or a SQL script that I can use to have this generated/exported to excel.

Thanks

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

syed baber 11,633 on at

Like (0)

Report

Hi BarryO,

Below is the code as per your requirement:

UserInfo userInfo;

UserGroupList userGrouplist;

while select id from userInfo

join GroupId from userGrouplist

where userInfo.id == userGrouplist.userId

{

info(strFmt("User %1 , User Group %2", userInfo.id, userGrouplist.groupId));

}

Please let me know if you have further queries.

Thanks,

Baber.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi BarryO

I am using below SQL code to generate active users list

with AU

as

(

SELECT SC.EMPLID, UI.NAME,

(SELECT TOP (1) DESCRIPTION

FROM dbo.HRPPARTYPOSITIONTABLERELAT2226 AS P

WHERE (DATAAREAID = 'DATAAREAID') AND (REFERENCE = SC.EMPLID)

ORDER BY VALIDFROMDATETIME DESC) AS Position,

(SELECT TOP (1) DESCRIPTION

FROM dbo.DIRPARTYINTERNALORGANIZATI2216 AS OU

WHERE (ORGANIZATIONUNITID IN

(SELECT TOP (1) ORGANIZATIONUNITID

FROM dbo.HRPPARTYPOSITIONTABLERELAT2226 AS P

WHERE (DATAAREAID = 'DATAAREAID') AND (REFERENCE = SC.EMPLID)

ORDER BY VALIDFROMDATETIME DESC)) AND (DATAAREAID = 'DATAAREAID')) AS Department, UI.NETWORKALIAS + '@' + UI.NETWORKDOMAIN AS Email,

UI.ID

FROM dbo.USERINFO AS UI INNER JOIN

dbo.SYSCOMPANYUSERINFO AS SC ON UI.ID = SC.USERID

WHERE (UI.COMPANY = 'DATAAREAID') AND (UI.ENABLE = 1) AND (SC.DATAAREAID = 'etis') AND (UI.NETWORKALIAS <> 'Ax Admin')

)

select *, STUFF((SELECT ', ' + cast(UGI1.GROUPID as varchar(10))

FROM USERGROUPLIST UGI1 where AU.Id = UGI1.USERID FOR XML PATH ('')) , 1, 1, '') AS [USER_GroupS]

from AU

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Baber

I receive this message when I execute this SQL Query.

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'select'.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'from'.

Thanks,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Au,

Thanks for the script , but i recieive this error message when I execute it.

Msg 263, Level 16, State 1, Line 37

Must specify table to select from.

Msg 4104, Level 16, State 1, Line 39

The multi-part identifier "AU.Id" could not be bound.

Thanks.

Was this reply helpful? Yes No
Suggested answer

syed baber 11,633 on at

Like (0)

Report

Hi BarryO,

This is not an SQL query that I provided. Run it in AX and you will get the desired results.

Please verify and let me know if you have further queries.

Thanks,

Baber.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi BarryO

please use below script and create an view.

change DataAreaId to you company id an filter user AX system account

CREATE VIEW [dbo].[ActiveUsers]

AS

SELECT SC.EMPLID, UI.NAME,

(SELECT TOP (1) DESCRIPTION

FROM dbo.HRPPARTYPOSITIONTABLERELAT2226 AS P

WHERE (DATAAREAID = 'DATAAREAID') AND (REFERENCE = SC.EMPLID)

ORDER BY VALIDFROMDATETIME DESC) AS Position,

(SELECT TOP (1) DESCRIPTION

FROM dbo.DIRPARTYINTERNALORGANIZATI2216 AS OU

WHERE (ORGANIZATIONUNITID IN

(SELECT TOP (1) ORGANIZATIONUNITID

FROM dbo.HRPPARTYPOSITIONTABLERELAT2226 AS P

WHERE (DATAAREAID = 'DATAAREAID') AND (REFERENCE = SC.EMPLID)

ORDER BY VALIDFROMDATETIME DESC)) AND (DATAAREAID = 'DATAAREAID')) AS Department,

UI.NETWORKALIAS + '@' + UI.NETWORKDOMAIN AS Email,

UI.ID

FROM dbo.USERINFO AS UI INNER JOIN

dbo.SYSCOMPANYUSERINFO AS SC ON UI.ID = SC.USERID

WHERE (UI.COMPANY = 'DATAAREAID') AND (UI.ENABLE = 1) AND (SC.DATAAREAID = 'DATAAREAID') AND (UI.NETWORKALIAS <> 'Ax Admin')

if you are able to create view then run below code

select *, STUFF((SELECT ', ' + cast(UGI1.GROUPID as varchar(10)) FROM USERGROUPLIST UGI1 where AU.Id = UGI1.USERID FOR XML PATH ('')) , 1, 1, '') AS [USER_GroupS]

from ActiveUsers AU

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Baber,

How do I run it in AX ? I'm somewhat new in AX and SQL.

Thanks again.

Was this reply helpful? Yes No
Suggested answer

syed baber 11,633 on at

Like (0)

Report

Hi Barry,

Open development workspace, go to AOT -> Jobs node. Right click jobs node and click new to create a new job. Paste the code I provided between opening and closing braces and run the job by pressing F5.

Please let me know if you have further queries.

Thanks,

Baber.

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

This SQL script will provide you your result;

SELECT UI.ID, UI.NAME, UGL.GROUPID

FROM USERINFO UI JOIN

USERGROUPLIST UGL ON

UGL.USERID = UI.ID

Please verify if it fulfills your requirements.

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

You just need to copy and paste above script in SQL server management studio. Press F5 it will show you all results and then you can copy and paste results directly into Excel.

Was this reply helpful? Yes No