Forum Discussion
SSO Not Working for Dataverse Datasource
djskro Adding/removing from the Power Apps interface doesn't have an impact on the security of the table at the dataverse layer. - Dataverse Security Roles (and in some cases, shares or inheritance) are the determining factor as to whether a user can view a row of data in a table.
Look at the security role(s) that the user has been granted - and within those roles, ensure the user has no 'read' permissions for the table. - (The dataverse security roles are additive, so if they have 20 roles and only one gives them organizational 'read' permissions - then they'll have read permission for the data in the table, it doesn't matter what the other roles have.)
Also, be sure to check to see if the user is a member of a team where a security role has granted them permission to read the table.
A really helpful tool for troubleshooting/understanding the security of a specific user on a specific record in Dataverse is the XrmToolBox "Access Checker" - Also, the "Privileges Discovery" tool in XrmToolBox will be useful.
(I don't think the lack of 'read' pemission on a table will block them from seeing that a table exists, but it should block the table from returning any data. )
For troubleshooting - open a VSCode or SSMS query window while logged in as that user - then query the table. - if the results are filtered as expected, you can move on to the next steps in troubleshooting. -
The settings in your original screenshot look correct. - be sure each table in the model that you want to filter is set to Direct Query (and not import or dual.)
You can also try using the SQL connector in power query and write your query as a native SQL statement Power Query Dataverse connector - Power Query | Microsoft Learn (The Dataverse connector is effectively a wrapper around the SQL connector - so I wouldn't assume it works any differently.)
Here's an example native query -
[Replaced - see follow up post]
djskro I realized the script I posted above is not my 'default' pattern -
This is what I use as the basis of every query:
let
Dataverse = CommonDataService.Database(TDSEndPoint,[CreateNavigationProperties=false]),
Source = Value.NativeQuery(Dataverse,"
SELECT [Base].accountid,
[Base].name [Customer Name]
FROM [dbo].[account] AS Base
" ,null ,[EnableFolding=true])
in
Source
(BTW, I have a parameter named "TDSEndPoint" with a value of "myEnvironment.crm.dynamics.com" in the report and I reference it for all queries - it just makes it easier to move from one destination to another.)
Thank you for this, Scott.
Going through these, what should we do if, in SSMS, logged in as a new test-account, we can, indeed, see and query the tables we do not want the test-account to be able to access (perhaps explaining our results in the Power BI report)?
How do we reconcile the screenshots below of the Power Apps interface with what is clearly visible to the user in SSMS?
djskro Adding/removing from the Power Apps interface doesn't have an impact on the security of the table at the dataverse layer. - Dataverse Security Roles (and in some cases, shares or inheritance) are the determining factor as to whether a user can view a row of data in a table.
Look at the security role(s) that the user has been granted - and within those roles, ensure the user has no 'read' permissions for the table. - (The dataverse security roles are additive, so if they have 20 roles and only one gives them organizational 'read' permissions - then they'll have read permission for the data in the table, it doesn't matter what the other roles have.)
Also, be sure to check to see if the user is a member of a team where a security role has granted them permission to read the table.
A really helpful tool for troubleshooting/understanding the security of a specific user on a specific record in Dataverse is the XrmToolBox "Access Checker" - Also, the "Privileges Discovery" tool in XrmToolBox will be useful.
(I don't think the lack of 'read' pemission on a table will block them from seeing that a table exists, but it should block the table from returning any data. )Thank you, Scott. This is helpful info.
