Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / UserQuery Table versus...
Customer experience | Sales, Customer Insights,...
Answered

UserQuery Table versus FilteredUserQueryView

(0) ShareShare
ReportReport
Posted on by Seamus 1,577

Hello, 

I am trying to migrate CRM Personal Views from 2013 onpremise to D365. The FilteredUserQuery View returns only a fraction of the data returned by querying the UserQuery Table.

Does anyone know what the difference is? More specifically a User has only one query in the FilteredUserQuery yet she has all her views including the single one displayed in FilteredUserQuery in the UserQueryTable - which seems to correspond to what she sees in her Saved Contact Views. 

I am hoping to use The View Transfer Tool (XRM Toolbox) - it works well but again only displays a fraction of the User Queries - presumably because it also FilteredUserQuery as its source, whereas I need to get access to all the Views referenced in the UserQuery Table,

I hope this makes sense - thanks in advance for any assistance.

- Seamus

  • Seamus Profile Picture
    Seamus 1,577 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Thanks to everyone,especially Justin, for all your help. Much appreciated!!

  • Verified answer
    Justinp Profile Picture
    Justinp 175 on at
    RE: UserQuery Table versus FilteredUserQueryView

    You will find the members of SQLAccessGroup group in AD, it will be called something like SQLAccessGroup GUID of your CRM Org and will be in the AD OU specified when CRM was installed.
    David sounds like he is on the ball with respect to the personal view permission. 
    I tested creating a personal view with a user account and was able to see it with my systems administrator account, but I may have tinkered with my security long ago.

    So lets get you a solution:
    Look at: https://debajmecrm.com/2014/07/31/dynamics-crm-personal-view-personal-views-created-by-other-users-in-microsoft-dynamics-crm/
    for a way to bulk share out personal views (Personal views in XRM Toolbox).  Once you share the views with your self you will be able to move them using the XRM toolbox tool.

    The Filtered view logic is here if you feel like going through it :)
    Won't help you much - but "fun to learn" ;)

    WHERE (pdm.PrivilegeDepthMask IS NOT NULL) AND (pdm.PrivilegeDepthMask & 0x8 <> 0) OR
    (pdm.PrivilegeDepthMask IS NOT NULL) AND (dbo.UserQuery.OwnerId IN
    (SELECT pem.PrincipalId
    FROM dbo.PrincipalEntityMap AS pem WITH (NOLOCK) INNER JOIN
    dbo.SystemUserPrincipals AS sup WITH (NOLOCK) ON pem.PrincipalId = sup.PrincipalId
    WHERE (sup.SystemUserId = u.SystemUserId) AND (pem.ObjectTypeCode = 4230))) OR
    (pdm.PrivilegeDepthMask IS NOT NULL) AND EXISTS
    (SELECT 1 AS Expr1
    WHERE (pdm.PrivilegeDepthMask & 0x4 <> 0) AND (dbo.UserQuery.OwningBusinessUnit IN
    (SELECT BusinessUnitId
    FROM dbo.SystemUserBusinessUnitEntityMap WITH (NOLOCK)
    WHERE (SystemUserId = u.SystemUserId) AND (ObjectTypeCode = 4230))) OR
    (dbo.UserQuery.OwningBusinessUnit IN
    (SELECT BusinessUnitId
    FROM dbo.SystemUserBusinessUnitEntityMap WITH (NOLOCK)
    WHERE (SystemUserId = u.SystemUserId) AND (ObjectTypeCode = 4230))) AND (pdm.PrivilegeDepthMask & 0x2 <> 0) OR
    (dbo.UserQuery.OwningBusinessUnit IS NOT NULL) AND (pdm.PrivilegeDepthMask & 0x8 <> 0)) OR
    (pdm.PrivilegeDepthMask IS NOT NULL) AND (dbo.UserQuery.UserQueryId IN
    (SELECT POA.ObjectId
    FROM dbo.PrincipalObjectAccess AS POA WITH (NOLOCK) INNER JOIN
    dbo.SystemUserPrincipals AS sup WITH (NOLOCK) ON POA.PrincipalId = sup.PrincipalId
    WHERE (sup.SystemUserId = u.SystemUserId) AND (POA.ObjectTypeCode = 4230) AND (POA.AccessRightsMask | POA.InheritedAccessRightsMask & 1 = 1)))

  • Justinp Profile Picture
    Justinp 175 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Duplicate post

  • Seamus Profile Picture
    Seamus 1,577 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Thanks David. Do you know of any way I can use impersonation. I tried it with kingswaysoft and it still did not work.

  • David Jennaway Profile Picture
    David Jennaway 14,065 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Any user (including System Administrators) only get user-level permission on the UserQuery entity - this is the mechanism used to have personal views, so a System Administrator would only see (either via the UI, or by querying the Filtered view) the views they own, or are shared with them.

    As far as I know, you'd need to use impersonation to impersonate each user in turn to read their views; I don't know if Andrew's tool includes this

  • Seamus Profile Picture
    Seamus 1,577 on at
    RE: UserQuery Table versus FilteredUserQueryView

    You are definitely right, Justin. Thanks! I checked the FilteredQuery View and what I see corresponds to the Personal Views which have been explicitly shared with me, so this is definitely a permissions issue, - as you identified early on. Re. The SQLAccessGroup do you know how I check if I'm in that group, - is it via the AD or Sql server mgmt studio? Many thanks again

  • Justinp Profile Picture
    Justinp 175 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Hmmm, now that is an interesting twist.  The user with the systems administrator role should see all the views (I checked, I do) so long as you have the correct read write license, as you are seeing some I expect you have the correct license.  Just a shot, is your user missing membership to one of the CRM AD groups (SQLAccessGroup)?

  • Seamus Profile Picture
    Seamus 1,577 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Thanks, yes you are right. I am logged in as a CRM system admin though?

  • Suggested answer
    Justinp Profile Picture
    Justinp 175 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Interesting, here is my hypothesis,

    I am guessing you have looked at or are looking at:

     -- SQL Table behind user created user views

     SELECT *

     FROM [mscf_dev_MSCRM].[dbo].[UserQueryBase]

     -- SQL View behind user created views

     SELECT *

     FROM [mscf_dev_MSCRM].[dbo].[FilteredUserQuery]

    And you are saying the results from the first query contain far more results than the second query?

    If this is the case then it is 100% a security thing... You are being prevented from seeing the views as you do not have rights to see them in the SQL filtered View

    Perhaps the user shared out the views you can see, and has not shared the views you can not see?

    Either way you will need to login with an account that has access to the views that need to be transferred.

    Can you get the user to share out one of the views you can not see, perhaps you can transfer it then?

  • Seamus Profile Picture
    Seamus 1,577 on at
    RE: UserQuery Table versus FilteredUserQueryView

    Thanks. I am looking at the database directly. One of our Users has 100s of personal views, all of which appear in the Table but only 5 of which appear in the FilteredView and also the XRM ToolBox View Transfer tool.  Therefore, unless I have configured it incorrectly, if the tool only allows me to migrate 5 out of 100s of personal views then I could not rate that as working very well.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Daivat Vartak (v-9davar) Profile Picture

Daivat Vartak (v-9d... 225 Super User 2025 Season 1

#2
Vahid Ghafarpour Profile Picture

Vahid Ghafarpour 78 Super User 2025 Season 1

#3
Sahra Profile Picture

Sahra 43

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans