locked
Database Filtered Views by SystemUserId? RRS feed

  • Question

  • I've written an external system which needs to query the database filtered views, FilteredAccount in this case.  I have the current user's CRM systemuserid so I want the view to only return account records that the user has access to (owns, team owns, shared to them, etc).  I know I could do "where FilteredAccount.ownerid = systemuserid" but that would only return the account record the user owns.  How do I query the view so all owned, team owned, shared, etc. are returned to the current user?

    Thanks

    Wednesday, March 6, 2013 2:19 AM

Answers

  • Hi,

    Filteredview returns data according to the user access. For example if you are using FilteredAccount in you report, CRM will return all those accounts where user has read permission (which includes account owned by user + accounts shared to the user + account shared to a team where user is one of the member).

    But if you are directly querying the database to know the accounts where user has the read pessimism/or user has access, then you need to query the database at three level.
    1. All owned records 
    Select AccountId from Account where ownerid= @systemuserid 
    2. All shared records
    Select ObjectId from PrincipalObjectAccess where PrincipalID=@systemuserid  and ObjectTypeCode=1 and (AccessRightMask > 0 OR inheratedAccessRightMask>0)
    3. All records shared to teams where user is a member. Combine the result for all teams (because user may be part of multiple team) 
    Select ObjectId from PrincipalObjectAccess where PrincipalID=@Teamid  and ObjectTypeCode=1 and (AccessRightMask > 0 OR inheratedAccessRightMask>0)

    Union the result from all above three steps. That will give you the accounts user has read access. 


    Chandan - http://mscrm-chandan.blogspot.in/ I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!

    Wednesday, March 6, 2013 8:33 AM

All replies

  • A much better option would perhaps be to use the CRM API to read the data using the data context provided and logging into CRM using the credentials of the user that needs to be used. This will ensure that all calls are made under the context of the user and automatically all permissions would be applied when the data is returned.

    If you would still prefer to perform your actions using SQL queries, you need to use FilteredViews as they are designed to consider the permissions assigned to the user under whose context the query is being executed and return appropriate results. One way to specify a user under the context of which the query should be executed you could try the Execute As option in SQL along with Revert.

    Note this will however add the specified user as a login to the database.

    HTH

    Sam


    Dynamics CRM MVP | Inogic | http://inogic.blogspot.com| news at inogic dot com

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Proposed as answer by Sam - Inogic Wednesday, March 6, 2013 3:21 AM
    Wednesday, March 6, 2013 3:21 AM
  • Hi,

    You can use SQL queries to return record ID's which is owned by the user or shared with the user. Consider example for Account Entity

    Select AccountId from Account where ownerid=''

    union

    Select ObjectId from PrincipalObjectAccess where PrincipalID='' and ObjectTypeCode=1 and AccessRightMask > 0

    These would return all accounts that user has access. You can check the access level for shared records using AccessRightMask of PrincipalObjectAccess table.

    Regards,

    Ravi

    Wednesday, March 6, 2013 7:43 AM
  • Hi,

    Filteredview returns data according to the user access. For example if you are using FilteredAccount in you report, CRM will return all those accounts where user has read permission (which includes account owned by user + accounts shared to the user + account shared to a team where user is one of the member).

    But if you are directly querying the database to know the accounts where user has the read pessimism/or user has access, then you need to query the database at three level.
    1. All owned records 
    Select AccountId from Account where ownerid= @systemuserid 
    2. All shared records
    Select ObjectId from PrincipalObjectAccess where PrincipalID=@systemuserid  and ObjectTypeCode=1 and (AccessRightMask > 0 OR inheratedAccessRightMask>0)
    3. All records shared to teams where user is a member. Combine the result for all teams (because user may be part of multiple team) 
    Select ObjectId from PrincipalObjectAccess where PrincipalID=@Teamid  and ObjectTypeCode=1 and (AccessRightMask > 0 OR inheratedAccessRightMask>0)

    Union the result from all above three steps. That will give you the accounts user has read access. 


    Chandan - http://mscrm-chandan.blogspot.in/ I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!

    Wednesday, March 6, 2013 8:33 AM
  • Exactly what I was looking for. Thank you!
    Wednesday, March 6, 2013 3:05 PM