security roles and user relations in AX 2012

This question has suggested answer(s)

Hello, Is there a way to export security roles assigned to users in AX - either excel or SSRS?

we can export the user's list but not the security roles assigned to each user info

user A - role1, role2 etc.,

user B - role2, role 3...

or what are the tables associated to users and their security roles?

appreciate your help

Raj 

www.seepath.com

Dynamics AX 2012  | CRM 2011 

 

All Replies
  • Hi Raj,

    The table name with this information is: SecurityUserRole

    You can find it in the AOT, System documentation, Tables

    Note that the company assingments are stored in the table SecurityUserRoleCondition.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

  • Hi Andre, thanks. i looked at the security role table that contain the security role ID but couldn't find the table that store the name  of the security role.

  • Hi Andre thanks. I found it. in fact the SecurityRole has the description that I can join with SecurityUserRole.

    thanks.

  • thanks Andre

  • If you create a query with these tables as datasources and add it in the form "Document data sources" (Organization administration, setup, document management) it is possible to use it in Excel as well with the Office add-in.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

  • Hi Raj,

    If you are running AX 2012 R2 or planning to upgrade to R2, this information is available in the standard SSRS report.

    Wonder why MS didnt provide the same report for AX 2012 RTM.

    Regards,

    Michael Brown

  • Michael

    Can you please explain how to access this SSRS report?

  • Please see the following blog entry for migrating security information in AX 2012

    blogs.msdn.com/.../migrating-security-settings-between-different-dynamics-ax-2012-instances.aspx

  • [System Administration > Reports > Licensing > Named User License Counts]

    I hope you have the scheduled batch job for calculating license counts running. To check it, look for the job [Named user license count reports processing] in [System Administration > Inquiries > Batch Jobs]

    Regards,

    Michael

  • i'm not sure on the report. But this query can return the user name, role and the permission they have in each company.

     SecurityRole securityRole;

        SecurityUserRole UserRole;

        SysUserInfo  userInfo;

        OMUserRoleOrganization roleOrganization;

        OMInternalOrganization internalOrganization;

        while select ID from userInfo

            order by id asc

            outer join UserRole

            where UserRole.User == userInfo.Id

                outer join securityRole

                where securityRole.RecId == UserRole.SecurityRole

                outer join roleOrganization

                where roleOrganization.User == userInfo.Id

                && roleOrganization.SecurityRole == UserRole.SecurityRole

                    outer join internalOrganization

                        where internalOrganization.RecId == roleOrganization.OMInternalOrganization

            {

                print userInfo.Id, userInfo.Email, SysLabel::labelId2String(securityRole.Name), internalOrganization.Name ? internalOrganization.Name : "All Company";

                pause;

        }

    - Kamal

     MyBlog | Twitter | LinkedIn

  • Question regarding the data...

    So I'm in the SQL SECURITYUSERROLE table and the SECURITYROLE column for my userID has the following values...

    735364

    735384

    735411

    735416

    735435

    I then go over to the SECURITYROLE table (I think it's a view though) and can't find any of these role values. I'm obviously missing another table somewhere. Can someone please enlighten me? Thanks in advance.

  • Hi Kbanh,

    You can find the security role table within the system documentation section of the AOT as well. I have checked the first RoleId from your list. Filtering on the record id of this table has a result: Role Employee.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.