Dynamics 365 Developers-Highlighting the Power of SQL for Dataverse

🚨 Dynamics 365 Developers — Have you ever tried to hide a field based on user roles using JavaScript… only to find the user magically has more roles than expected?

Recently, I was working on a requirement to hide a field in a Model-Driven App based on a custom security role (e.g. "Manage Role") using a JavaScript function. But when I pulled the logged-in user's roles, I got 5 roles, even though only one was explicitly assigned.

That’s when I dug deeper and uncovered that roles were being inherited via team membership, something not immediately visible in the UI..💡

🔍 Why You Might See Extra Roles in JavaScript (userSettings.roles)

1. Team-Based Roles: If a user belongs to a team that has assigned roles, they inherit those roles too — and they show up in your client-side scripts.
2. Default/System Roles: Users may also have roles like "Basic User", "Environment Maker", etc., depending on your environment setup.

💥 But here's the real power move:

Instead of clicking through multiple admin pages, user forms, and team memberships to figure this out...

👉 I used SQL for CDS (via XrmToolBox) to query the backend tables of Dataverse — just like regular SQL.

🔍 No clicks. No UI switching. Full visibility — instantly.

🛠️ SQL Queries That Tell You Everything — in One Place

🔹 Get User Info:

SELECT TOP 5 systemuserid, firstname, fullname, businessunitidname, domainname AS EMAIL, internalemailaddress 
FROM systemuser  
WHERE firstname LIKE '%Amit%'
        

🔹 Find All Roles Assigned Directly to the User:

SELECT C.domainname AS EMAIL, B.name AS ROLENAME
FROM systemuserroles A
JOIN role B ON A.roleid = B.roleid
JOIN systemuser C ON A.systemuserid = C.systemuserid
WHERE C.internalemailaddress LIKE '%Amit%'        

🔹 Check Teams the User Is Part Of:

SELECT C.name AS TeamName, B.domainname 
FROM teammembership A
JOIN systemuser B ON A.systemuserid = B.systemuserid 
JOIN team C ON A.teamid = C.teamid 
WHERE B.systemuserid LIKE '%07AEFD01-4962-ED11-9562-000D3A0A6E93%'        

🔹 Check Roles Inherited via Teams:

SELECT C.name AS TeamName, B.domainname AS User, R.name AS RoleName
FROM teammembership A
JOIN systemuser B ON A.systemuserid = B.systemuserid
JOIN team C ON A.teamid = C.teamid
JOIN teamroles TR ON TR.teamid = C.teamid
JOIN role R ON TR.roleid = R.roleid
WHERE B.systemuserid LIKE '%07AEFD01-4962-ED11-9562-000D3A0A6E93%'        

🧠 Why This Matters

🔹 UI shows roles one by one — SQL shows the complete picture 🔹 You can automate analysis, not just click through forms 🔹 No navigating multiple admin pages — just query and go

If you're not using SQL 4 CDS in XrmToolBox, you're missing one of the most powerful Dev/Admin tools in the Dataverse ecosystem.

💬 Curious — do you use SQL for CDS regularly in your Power Platform work? Or are you still sticking to the UI to inspect user security? Let’s chat!

#PowerPlatform #Dynamics365 #Dataverse #SQL4CDS #XrmToolBox #SecurityRoles #DeveloperTools #LowCode #ModelDrivenApps

Would you like me to make this post even more visual with a screenshot or step-by-step GIF for how to run this in XrmToolBox? I can help you make it a scroll-stopper 🔥