Wednesday, March 06, 2013 2:19 AM
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?
Wednesday, March 06, 2013 3:21 AM
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.
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"
- Proposed As Answer by Sam - InogicMVP Wednesday, March 06, 2013 3:21 AM
Wednesday, March 06, 2013 7:43 AM
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=''
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.
Wednesday, March 06, 2013 8:33 AM
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 06, 2013 3:05 PMExactly what I was looking for. Thank you!