CRM 2016 Database Direct SQL Query Form VS Security Role

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi all,

I would like to create a table matrix in SSRS to show relationship between Forms and Security Roles

Some thing like below:

Form/Role	Manager	Sale	Financial
Form 1	x
Form 2	x	x
Form 3	x		x

And I want SQL query to query the CRM filtered views, but I don't know which views have that relationship. I only see FilteredSystemForm, but I am expecting to see a view something like FilteredSystemFormRoles that is similar to FilteredSystemUserRoles which stores relationship between users and roles.

Any advice?

Thanks,

Sam

*This post is locked for comments

All responses (5)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: CRM 2016 Database Direct SQL Query Form VS Security Role

Broken link.. try this

github.com/.../Dynamics-CRM
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: CRM 2016 Database Direct SQL Query Form VS Security Role

MARKED AS ANSWERED!!

github.com/.../Entity&;s%20Form%20Based%20Security%20Roles.sql

LOL.. just try this anyway. Someone developed a SQL and it might solve your pain.
Community Member Microsoft Employee on at

Like (0)

Report

RE: CRM 2016 Database Direct SQL Query Form VS Security Role

David, do you know how do I get the objectTypeCodeName in the FilteredSystemForm? it is null now, I wonder if there is a table or view that storing those information
Community Member Microsoft Employee on at

Like (0)

Report

RE: CRM 2016 Database Direct SQL Query Form VS Security Role

Thanks David! this mean I have to lookup the role id inside the formxml field, this sucks tho :(
David Jennaway 14,063 on at

Like (1)

Report

RE: CRM 2016 Database Direct SQL Query Form VS Security Role

This information is stored within the FormXml in FilteredSystemForm - it's in the <form><DisplayConditions><role> element(s)