Dynamic data forms and reports from non-Drupal databases I have the task of displaying non-Drupal data which is stored in SQL Server within Drupal. I need to display search pages (ad-hoc style) and results based on page and field definitions stored in a SQL Server database. I will also need to display them based on permissions defined in the SQL Server database. This information will be a copy of the users and organic groups from the Drupal system, but will be stored in SQL Server so that they are available to other systems leveraging the same data, so in essence it can be considered a completely separate system. 

I need to read the list of permissions (which will be managed down to the field level) and dynamically manage the display of fields, in both search criteria and result sets. For example, if Form1 has 10 fields and 2 of them require you to be an admin to see, there will be the following tables (structure hasn't been decided so there are just samples):

tbl_forms: form_id, form_name
tbl_fields: field_id, form_id, field_name
tbl_users: uid, username, etc
tbl_role_permissions: 
 - roleperms_id
 - element_id (id of form or field)
 - element_type (form or field)
 - roles_show (an array of roles that have access to see this form or field)
 - roles_edit (an array of roles that have access to edit this form or field)


With this information, I believe it's possible to create an ad-hoc reporting system with roles level control of who can see individual fields for editing or in the data results of searches. I may need to also add a roles_search field, but I can't think of anything else.

Is there an existing approach to displaying external data dynamically within Drupal? If not, does this type of structure appear granular enough for effective creation of this type of external data management and reporting system?
Consider the Forena module, not many people know about it, which is why I wanted to mention it here.

Forena is built of the idea of using SQL to get data out of a database and use XHTML and CSS to format it into web reports. 

Various DBMSs are supported, such as Oracle, MS Sql, Postgress, SQLite, MySQL, any PDO compliant DBMS ... And there are quite some reasons for considering Forena.

Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links.

Note: I'm a co-maintainer of Forena.