Creating a view that filters list items based on user permissions using CAML

Recently I got a task that was about creating a SharePoint view that displays only list items that the logged-in user has editor permissions for. Sounds a bit tough at first, but if you have ever heard about the PermMask field (not very well documented, but you can read more about that here and here) then it gives you some hope.

So I’ve created a test list (called SecTest) and created two items. One of my test users has admin rights, the second one has editor permission for the first item and read permission for the second one. The list inherits the permissions of the parent site, the permission inheritance is broken for the second item.

First I’ve tried to use SharePoint designer to customize the existing All Items view of the list.

Added the PermMask to the FieldRefs:

<FieldRef Name="PermMask" />

And created the filter part for the Query:

<Where>
    <Eq>
        <FieldRef Name=’PermMask’ />
        <Value Type=’Computed’>somevalue</Value>
    </Eq>
</Where>

(I’ve tried different permission mask values, all with the same result.)

Saving the view in SPD resulted the following warning:

Pressing the Details I’ve got the following message:

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.0x80131904

(I’ve tried to use Text instead of Computed as the Type attribute of the Value in the Where section of the CAML expression, but it did not help.)

Checking the view in the browser produced an error as well.

I’ve found the following related error message in the SharePoint log (one for the saving in SPD and another one for opening the view in IE):

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.HasMoreRows()     at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadl…    
…ock)    
Unable to execute query: Error 0x80131904    
Unknown SPRequest error occurred. More information: 0x80131904    
<nativehr>0x80131904</nativehr><nativestack></nativestack>    
<nativehr>0x80131904</nativehr><nativestack></nativestack>    

FYI, the hexadecimal value causing the conversion error (see the bolded section above, 0xb008431061 = 756052856929 in decimal) corresponds the permission mask of the Read permission level (see the value of the PermMaskfield in the Roles table of the content database, where in the record where Title is Read, that is SELECT PermMask FROM [YourContentDB].[dbo].[Roles] WHERE Title = ‘Read’)

Next, I’ve tried the same from code using a test console application:

1. SPList list = web.Lists["SecTest"];
2. SPQuery query = new SPQuery();
3. // tried with and without these lines
4. //query.IncludeAllUserPermissions = true;
5. //query.IncludePermissions = true;
6. query.Query = "<Where><Eq><FieldRef Name='PermMask' /><Value Type='Computed'>0xb008431061</Value></Eq></Where>";
7. query.ViewFields = "<ViewFields><FieldRef Name='PermMask' /></ViewFields>";
8.  
9. SPListItemCollection items = list.GetItems(query);
10.  
11. // exception is thrown at the next line
12. foreach (SPItem item in items)
13. {
14.     Console.WriteLine(item["PermMask"]);
15. }

The code produced the same error.

I’ve started SQL Server Profiler to create a trace of the SQL side during the code execution. The trace captured the following errors:

Exception
Error: 245, Severity: 16, State: 1
User Error Message
Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.

The source of the error was the following parameterized query:

exec sp_executesql N’DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;   SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L7 AND tp_ID = @L2 UNION SELECT t1.ScopeId FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND (UserData.tp_ListId = @L2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND ((UserData.[tp_ID] = N”0xb008431061”) AND t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF))) OPTION (MAXDOP 1)’,N’@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@RequestGuid uniqueidentifier,@L7 uniqueidentifier’,@LFFP=’00000000-0000-0000-0000-000000000000′,@SITEID=’D2F4F094-9626-4B84-87D3-260B6B9213EF’,@L2=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@FDN=N’Lists’,@FLN=N’SecTest’,@LISTID=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@RequestGuid=’E75EC6C1-5CDE-4368-B55C-A9AAA661E539′,@L7=’3ED0DC82-1AC6-4A29-A177-56DA059B2EF8′

I’ve found that removing the bolded condition above helps to run the query without any exception.

In my case the result was (or would be?)  the next one:

The ScopeId field identifies the entity the permission is set on. See the Perms table in the content DB to decode it. In my case the first row is related to the root web, the second on is to the second item in the list, for which we set individual permissions. The Acl field stores the access control list for the entity in a binary format.

It seems that if you include the PermMask field in the CAML query, SharePoint tries to read up the related permission settings but fails due to a bug.

I’ve tried to locate the source of the issue, but was not fully successful. From the stack trace it seems that the ExecuteQueryInternal method of the internal SPSqlClient class (Microsoft.SharePoint namespace, Microsoft.SharePoint assembly) is called to run the SQL query, this method calls the ExecuteReader method of the Microsoft.SharePoint.Utilities.SqlSession, but in the ReadInternal method of System.Data.SqlClient.SqlDataReader the exception is thrown. However I’ve not found the method where the query is incorrectly assembled. I assume it is related to the SetCommand method of the SPSqlClient class but I’ve not yet found the caller of the method. The signature of the method (IntPtr queryText, uint lenQueryText, bool isStoredProc) and the Marshal.PtrToStringUni call within the method suggest that the caller might be even an unmanaged code. See the similar SetCommand method of the SPSqlClientClass class (Microsoft.SharePoint.Library namespace, Microsoft.SharePoint.Library assembly):

[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType=MethodCodeType.Runtime)]
public virtual extern void SetCommand([In] IntPtr pstrQueryText, [In] uint cchQueryText, [In] bool bStoredProcedureSyntax);

Very likely that I have to find an alternative approach to create my permission based view. I have some further ideas, but more about them in a possible post later.