Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

SECURITYROLES table missing from SQL?

(0) ShareShare
ReportReport
Posted on by 170

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:

7510.AOT.jpg

7024.SQL.jpg

...and this is what I get when I try typing SECURITY into SQL:

34048.Capture.JPG

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SECURITYROLES table missing from SQL?

    I Cannot find table "securityrole" but can find other tables like SecurityUserRole, UserInfo  etc ? what could be the reason

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SECURITYROLES table missing from SQL?

    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

    ;

  • SpoonMan Profile Picture
    SpoonMan 170 on at
    RE: SECURITYROLES table missing from SQL?

    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).

    7673.Capture.JPG

  • Rati Sharabidze Profile Picture
    Rati Sharabidze 612 on at
    RE: SECURITYROLES table missing from SQL?

    Hi Dan,

    Check this link.

    community.dynamics.com/.../208285

    Best regards

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SECURITYROLES table missing from SQL?

    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.

  • SpoonMan Profile Picture
    SpoonMan 170 on at
    RE: SECURITYROLES table missing from SQL?

    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 :-(

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SECURITYROLES table missing from SQL?

    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.

  • SpoonMan Profile Picture
    SpoonMan 170 on at
    RE: SECURITYROLES table missing from SQL?

    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:

    7245.Capture.JPG

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,031 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,868 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans