SQL 4 CDS - querying teamroles

 

In my previous post on the SQL 4 CDS XRMToolbox tool, Dynamics 365 tool you should use — SQL 4 CDS, I used it to check for deactivated workflows, get versions and export data.

I have been using it to verify my releases have worked correctly.

After I run a release I check for deactivated workflows and business rules.

I am using AD Group and AAD Security group teams (Dynamics 365 and AD groups). A quick recap is you link AD groups to AAD security groups teams in Dynamics 365 and when you add a user into an AD group it automatically adds it the team in Dynamics. The key to setting this is up is to make sure you set the the correct security roles.

The PowerObjects data tool allows to import data into different environments, this means I can keep the same team but change the Azure ID to the team and link to a different AD group for each environment.

So after a release and moving data I wanted to check the right number of roles were assigned to teams. When you link a a role to a team it updates the teamroles table, this is hidden from advanced find but you can see it using SQL 4 CDS.

The PowerObjects data tool can also assign the roles to teams which is great, so I want to make sure they are setup correctly in all environments. If you non production environments are the same as prod, you can find problems quicker and before customisations get into productions, so keep you environments in sync and fix problems when they are small.

This is how I made the file and checked the values in the teamroles table. Interesting things to note, the format button works well and tidied up my query. This query is my first double join!!! The query joins the teamrole to teams and roles. I only need the roleid, teamid but it’s easier for a human to read actual roles and team names

SELECT tr.roleid,

tr.teamid,

‘TRUE’ AS associate,

t.name,

r.name

FROM teamroles AS tr

INNER JOIN

role AS r

ON tr.roleid = r.roleid

INNER JOIN

team AS t

ON t.teamid = tr.teamid

WHERE r.name <> ‘System Administrator’

ORDER BY t.name, r.name;

I exclude the System Administrator because I can’t set that between environments. The System Administrator role has a different guid in each environment!

first query gets all the team roles, in my case it should equal 129 or something is wrong

SELECT count(teamroleid)

FROM teamroles;

I wanted to check the different AAD teams had the right number of roles, my first attempt at grouping and counts, Yes that works with SQL 4 CDS

this produce this result

This query did some grouping and counting

select t.name, count(tr.teamid) as RoleCount

from teamroles as tr

INNER JOIN

team AS t

ON t.teamid = tr.teamid

where t.teamtype = ‘2’

Group by t.name

result

SELECT t.name,

r.name

FROM teamroles AS tr

INNER JOIN

role AS r

ON tr.roleid = r.roleid

INNER JOIN

team AS t

ON t.teamid = tr.teamid

WHERE r.name <> ‘System Administrator’

and teamtype = ‘2’

ORDER BY t.name, r.name;

Result

The reason I ignore ‘System Administrator’ is you can’t move this role between environments