security roles and user relations in AX 2012

Question Status

Suggested Answer
CRM Manager asked a question on 27 Apr 2013 6:33 AM

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

Reply
Suggested Answer
André Arnaud de Calavon responded on 27 Apr 2013 11:47 AM

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.

Reply
ewills responded on 27 Apr 2013 11:51 AM

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.

Reply
ewills responded on 27 Apr 2013 11:54 AM

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

thanks.

Reply
Suggested Answer
ewills responded on 27 Apr 2013 12:00 PM

thanks Andre

Reply
Suggested Answer
André Arnaud de Calavon responded on 27 Apr 2013 12:16 PM

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.

Reply
MBrown1185 responded on 29 Apr 2013 12:06 AM

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

Reply
pgattu responded on 7 May 2013 1:47 PM

Michael

Can you please explain how to access this SSRS report?

Reply
Parth Pandya responded on 7 May 2013 2:44 PM

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

Reply
MBrown1185 responded on 7 May 2013 9:07 PM

[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

Reply
Suggested Answer
Kamalakannan Elangovan responded on 12 May 2013 12:04 PM

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;

    }

Reply
KBanh responded on 11 Jul 2013 1:53 PM

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.

Reply
André Arnaud de Calavon responded on 15 Jul 2013 11:55 PM

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.

Reply
Kauto responded on 23 Sep 2014 11:08 AM

Is it possible to create a binary def group and use the import / export framework to import the data from SecurityUserRole table if the userInfo is the same in each system?

I am creating a UAT environment and I want to transfer the user role assignments already setup in the first environment.

Reply
Dan Ladle responded on 3 Oct 2016 1:21 AM

I assume the other guys sorted this for you, but I thought this SQL query I made might be useful!

Cheers

Dan

/*
	Imagineered and coded by Dan Ladle – 14/09/2016
*/

Use MicrosoftDynamicsAX

/*
	Select information from relevant tables and fields and give them meaningful names. 
		Comment out lines by placing /* in front of them and */ behind them if you don't want them included.
*/

SELECT	userinfo.NAME as "Full Name",
		userinfo.NETWORKALIAS as "User ID",
		sysuserinfo.EMAIL as "Email Address",
		/* 
			The following code chooses data from a different column if the Security role includes "SYS", "RIN" OR "TRA".
		*/
		CASE WHEN MicrosoftDynamicsAX_model.dbo.securityrole.DESCRIPTION LIKE '%SYS%'
			OR MicrosoftDynamicsAX_model.dbo.securityrole.DESCRIPTION LIKE '%RIN%' 
			OR MicrosoftDynamicsAX_model.dbo.securityrole.DESCRIPTION LIKE '%TRA%' THEN
			(SELECT MicrosoftDynamicsAX_model.dbo.securityrole.AOTNAME COLLATE Latin1_General_CI_AS)
		ELSE
			(SELECT MicrosoftDynamicsAX_model.dbo.securityrole.DESCRIPTION)
		END as "Security Roles",
		sysuserprofiles.PROFILEID as "User Profiles",
		usergroupinfo.NAME as "User Groups",
		userinfo.LANGUAGE as "Language",
		HELPLANGUAGE as "Alternate Help Language",
		sysuserinfo.DEFAULTCOUNTRYREGION as "Default Country/Region",
		userinfo.COMPANY as "Start Company Accounts",
		CASE userinfo.ENABLE
			WHEN '1' THEN 'Enabled'
			WHEN '0' THEN 'Disabled'
		END as "Enabled"
FROM USERINFO

/*
	Allow data to be amalgamated from relevant tables.
*/

LEFT JOIN sysuserinfo
	ON userinfo.ID = sysuserinfo.ID LEFT JOIN sysuserprofiles
		ON sysuserprofiles.USERID = userinfo.ID LEFT JOIN usergrouplist
			ON usergrouplist.USERID = userinfo.ID LEFT JOIN usergroupinfo
				ON usergroupinfo.ID = usergrouplist.GROUPID LEFT JOIN securityuserrole
					ON securityuserrole.USER_ = userinfo.ID LEFT JOIN MicrosoftDynamicsAX_model.dbo.securityrole
						ON MicrosoftDynamicsAX_model.dbo.securityrole.RECID = securityuserrole.SECURITYROLE

/*
	Include only records where the user's Full Name field is not blank.
		If you want results for a specific user then change the second condition from LIKE to = and enter their username between the quote marks. 
		If you're after users with a particular role change the third condition from != to = and enter the security role between the quotes.
			n.b. Please note the security roles are pulled from two different columns so it's worth checking what you need to use if searching for that.
*/

WHERE userinfo.NAME != '' 
	AND userinfo.ID LIKE '%'
	AND MicrosoftDynamicsAX_model.dbo.securityrole.DESCRIPTION != ''

/*
	Order the results by Full Name, User ID, Security Roles, User Profiles then User Groups.
		n.b. Because Security Roles are pulled from two different places depending on the name they are organised by Created roles then system roles.
*/

ORDER BY userinfo.NAME,
	userinfo.ID,
	MicrosoftDynamicsAX_model.dbo.securityrole.DESCRIPTION,
	sysuserprofiles.PROFILEID,
	usergroupinfo.NAME


Reply
Suggested Answer
André Arnaud de Calavon responded on 27 Apr 2013 11:47 AM

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.

Reply
Suggested Answer
ewills responded on 27 Apr 2013 12:00 PM

thanks Andre

Reply
Suggested Answer
André Arnaud de Calavon responded on 27 Apr 2013 12:16 PM

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.

Reply
Suggested Answer
Kamalakannan Elangovan responded on 12 May 2013 12:04 PM

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;

    }

Reply