Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Which SQL table are us...
Microsoft Dynamics CRM (Archived)

Which SQL table are user roles in?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I am using Skyvia to connect to Dynamics 365 and synchronize users and their roles with a MySQL database.

I can see in the SQL database for Dynamics that users data is within the 'systemuser' table. 

Can someone tell me which table I get the users roles from please? Within CRM I can see users and their relevant roles/groups but can't see where this is held within SQL.

Thank you in advance!

*This post is locked for comments

  • Suggested answer
    Kokulan Profile Picture
    Kokulan 18,048 on at
    RE: Which SQL table are user roles in?

    Hi

    dbo.SystemUserRoles is the intermediate table in CRM SQL database that holds the roles associated with users.

    ScreenClip-_5B00_119_5D00_.png

    If the table is not their in your MySQL DB, it could that you have to re-configure the tool to Sync this table as well. Or the tool might be creating the intermediate table with different name.  If you check the columns of all the tables, you will be able to find out.

  • Suggested answer
    Kalpavruksh D365 CoE Profile Picture
    Kalpavruksh D365 CoE 2,545 on at
    RE: Which SQL table are user roles in?

    systemuserroles is a filteredview.

    You can get the information in SQL under filteredviews with "filtered" prefix as well as under tables with the above names.

    If found useful, please mark this answer as verified.

  • Dynamics365 Rocker Profile Picture
    Dynamics365 Rocker 7,755 on at
    RE: Which SQL table are user roles in?

    Check if you find filtered view with same name.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Which SQL table are user roles in?

    Thanks for the reply. I can find the first 2 in the SQL database, but not the 3rd "systemuserroles"

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Which SQL table are user roles in?

    Thanks, I can't find the RoleBase table in the SQL database

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Which SQL table are user roles in?

    Thanks, I can't find either of these in the Dynamics365 Online SQL tables though.

  • Suggested answer
    Kalpavruksh D365 CoE Profile Picture
    Kalpavruksh D365 CoE 2,545 on at
    RE: Which SQL table are user roles in?

    Hi,

    There are 3 tables: 

    1. Systemuser- user records and related info is stored

    2. role - roles with access rights and privileges

    3. systemuserroles - this contains information of users with the assigned roles

    You can get the information in SQL under filteredviews with "filtered" prefix as well as under tables with the above names.

    Please mark this answer as verified, if it has resolved your question.

  • Suggested answer
    Abhishek Gupta Profile Picture
    Abhishek Gupta 2,001 on at
    RE: Which SQL table are user roles in?

    Hi

    Check select * from RoleBase. Entity name is RoleBase.

    Thank you

    Abhishek

    If found useful, please mark the answer as verified

  • Suggested answer
    Dynamics365 Rocker Profile Picture
    Dynamics365 Rocker 7,755 on at
    RE: Which SQL table are user roles in?

    SytemUserRole or UserRole.

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans