{"id":5839,"date":"2013-07-23T12:15:39","date_gmt":"2013-07-23T12:15:39","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-query-for-determining-sharepoint-acl-sizes\/"},"modified":"2016-07-28T10:53:43","modified_gmt":"2016-07-28T10:53:43","slug":"sql-query-for-determining-sharepoint-acl-sizes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-query-for-determining-sharepoint-acl-sizes\/","title":{"rendered":"SQL Query for Determining SharePoint ACL Sizes"},"content":{"rendered":"

When a SharePoint Access Control List (ACL) size exceeds more than 64kb for a particular URL, the contents under that URL become unsearchable due to limitations in the SharePoint search engine.  The error most often seen is The Parameter is Incorrect<\/strong> which really helps to pinpoint the problem (its difficult to convey extreme sarcasm here, please note that it is intended).  Exceeding this limit is not unheard of – it can happen when users brute force security into working by continually overriding inherited permissions and assigning user-level access to securable objects.<\/p>\n

Once you have this issue, determining where you need to focus to fix the problem can be difficult.  Fortunately, there is a query that you can run on a content database that can help identify the issue:<\/p>\n

SELECT [SiteId],
     <\/code>MIN([ScopeUrl]) AS URL,
     SUM(DATALENGTH([Acl]))\/1024 as AclSizeKB,
     COUNT(*) AS AclEntries
FROM [Perms] (NOLOCK)
GROUP BY siteid
ORDER BY AclSizeKB DESC <\/code> <\/p>\n

This query results in a list of ACL sizes and entry counts on a site-by-site basis.  You can also remove grouping to see a more granular breakdown:<\/p>\n

SELECT [ScopeUrl] AS URL, 
     SU<\/code>M(DATALENGTH([Acl]))\/1024 as AclSizeKB,
     COUNT(*) AS AclEntries
<\/code>FROM [Perms] (NOLOCK)
GROUP BY ScopeUrl
ORDER BY AclSizeKB DESC <\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

When a SharePoint Access Control List (ACL) size exceeds more than 64kb for a particular URL, the contents under that URL become unsearchable due to limitations in the SharePoint search engine.  The error most often seen is The Parameter is Incorrect which really helps to pinpoint the problem (its difficult to convey extreme sarcasm here,……<\/p>\n","protected":false},"author":46738,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-5839","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/5839","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/46738"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=5839"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/5839\/revisions"}],"predecessor-version":[{"id":42310,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/5839\/revisions\/42310"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=5839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=5839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=5839"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=5839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}