Alright Dudes,
I'm just starting to put a query together within SQL and I don't seem to be able to find the SECURITYROLE table within the database. It certainly exists as I can query it directly from the AOT but when I use SQL Server Management Studio the same table doesn't show up.
It's entirely possible I'm doing (or not doing) something silly that I should already know about but for the life of me I can't figure out where I'm going wrong. Here are a couple of screenshots just to show you what I'm on about:
...and this is what I get when I try typing SECURITY into SQL:
As always, any help greatly appreciated, you lot are brilliant.
Cheers
Dan
For is it not written, "we live and learn"?
*This post is locked for comments
I Cannot find table "securityrole" but can find other tables like SecurityUserRole, UserInfo etc ? what could be the reason
select SecurityUserRole.user_ , userinfo.Name , securityRole.aotname
from [MicrosoftDynamicsAX_model].[dbo].[SECURITYROLE] ,
[MicrosoftDynamicsAX].[dbo].[SECURITYUSERROLE] ,
[MicrosoftDynamicsAX].[dbo].[userinfo]
where securityRole.RecId = SecurityUserRole.SecurityRole and
userinfo.id = SecurityUserRole.user_ and
userinfo.enable = 1
order by SecurityUserRole.user_ , securityRole.aotname
;
Sorry for doubting, you're absolutely correct (it's been a very long day) :-)
This is the query as it currently looks (and definitely works).
Ok, if you will look at views in model data base you will find securityrole, securityTask and all other table that you can see in system documentation node in AOT.
However, they are based on ModelSecurity* tables under the hood.
I'm afraid that is completely different data to what I'm looking for. As you can see from my query, I'm trying to pull all the useful data about users into one simple table. The thing I want to add is the Security roles assigned to each user but the ModelSecurityRole table seems to consist of permissions in roles, rather than the titles of the roles themselves :-(
Hi Dan Ladle,
Just check model store data base (it has name that usually is you AX data base name + "_model") .
There you can find tables like ModelSecurityRole, ModelSecuritySubRole and so on.
Oh yeah, just in case you're interested. This is what I have so far:
Use MicrosoftDynamicsAX
SELECT DISTINCT userinfo.NAME as "Full Name",
sysuserinfo.EMAIL as "Email Address",
userinfo.ID as "User ID",
sysuserprofiles.PROFILEID as "User Profile ID",
usergroupinfo.NAME as "User Group ID",
userinfo.LANGUAGE as "Language",
HELPLANGUAGE as "Alternate Help Language",
sysuserinfo.DEFAULTCOUNTRYREGION as "Default Country/Region",
userinfo.COMPANY as "Start Company Accounts"
FROM USERINFO
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
WHERE userinfo.NAME != ''
ORDER BY userinfo.NAME, usergroupinfo.NAME
I can JOIN SECURITYUSERROLE easily enough but that's not a lot of good if I can't also use SECURITYUSER.
This is how it looks in situ:
André Arnaud de Cal...
292,031
Super User 2025 Season 1
Martin Dráb
230,868
Most Valuable Professional
nmaenpaa
101,156