Which SQL table are user roles in?

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Dynamics365 Rocker 7,755 on at

Like (0)

Report

SytemUserRole or UserRole.

Was this reply helpful? Yes No
Suggested answer

Abhishek Gupta 2,003 on at

Like (0)

Report

Hi

Check select * from RoleBase. Entity name is RoleBase.

Thank you

Abhishek

If found useful, please mark the answer as verified

Was this reply helpful? Yes No
Suggested answer

Kalpavruksh D365 CoE 2,545 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

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

Was this reply helpful? Yes No
Dynamics365 Rocker 7,755 on at

Like (0)

Report

Check if you find filtered view with same name.

Was this reply helpful? Yes No
Suggested answer

Kalpavruksh D365 CoE 2,545 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Kokulan 18,054 on at

Like (0)

Report

Hi

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

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.

Was this reply helpful? Yes No